$qb = $conn->createQueryBuilder()
->select('u.name')
->from('users', 'u')
->join('u', 'phonenumbers', 'p', 'p.is_primary = 1');
/** * Apply where filters to the query * * @param QueryBuilder $query * @param array $filters */ public function applyFilters(&$query, $filters) { if ($filters && is_array($filters)) { foreach ($filters as $column => $value) { $valId = $column . '_val'; // Special case: Lead list filter if ($column === 'leadlist_id') { $query->join('t', MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 'lll', 'lll.lead_id = ' . $value['list_column_name']); $query->andWhere('lll.leadlist_id = :' . $valId); $query->setParameter($valId, $value['value']); } elseif (isset($value['expression']) && method_exists($query->expr(), $value['expression'])) { $query->andWhere($query->expr()->{$value['expression']}($column)); if (isset($value['value'])) { $query->setParameter($valId, $value['value']); } } else { if (is_array($value)) { $query->andWhere('t.' . $column . ' IN(:' . $valId . ')'); $query->setParameter($valId, implode(',', $value)); } else { $query->andWhere('t.' . $column . ' = :' . $valId); $query->setParameter($valId, $value); } } } } }
/** * {@inheritdoc} */ public function buildQuery(QueryBuilder $queryBuilder, array $values = []) { if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) { return; } $specialities = $values[$this->getName()]->toArray(); $specialities = array_map(function (Speciality $item) { return $item->getId(); }, $specialities); $queryBuilder->join('response', 'response_speciality', 'response_speciality', 'response.id = response_speciality.response_id')->join('response_speciality', 'speciality', 'speciality', 'response_speciality.speciality_id = speciality.id')->andWhere($queryBuilder->expr()->in('speciality.id', $specialities)); }
/** * Joins the page table and limits created_by to currently logged in user * * @param QueryBuilder $q */ public function limitQueryToCreator(QueryBuilder &$q) { $q->join('t', MAUTIC_TABLE_PREFIX . 'dynamic_content', 'd', 'd.id = t.dynamic_content_id')->andWhere('d.created_by = :userId')->setParameter('userId', $this->user->getId()); }
/** * Joins the email table and limits created_by to currently logged in user * * @param QueryBuilder $q */ public function limitQueryToCreator(QueryBuilder &$q) { $q->join('t', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'e.id = t.email_id')->andWhere('e.created_by = :userId')->setParameter('userId', $this->user->getId()); }
/** * Creates and adds a join to the query. * * <code> * $qb = $conn->getQueryBuilder() * ->select('u.name') * ->from('users', 'u') * ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance. */ public function join($fromAlias, $join, $alias, $condition = null) { $this->queryBuilder->join($fromAlias, $this->getTableName($join), $alias, $condition); return $this; }
/** * Joins the page table and limits created_by to currently logged in user. * * @param QueryBuilder $q */ public function limitQueryToCreator(QueryBuilder &$q) { $q->join('t', MAUTIC_TABLE_PREFIX . 'pages', 'p', 'p.id = t.page_id')->andWhere('p.created_by = :userId')->setParameter('userId', $this->userHelper->getUser()->getId()); }
/** * Creates and adds a join to the query. * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return self */ public function join($fromAlias, $join, $alias, $condition = null) { $this->qb->join($fromAlias, $join, $alias, $condition); return $this; }
/** * Creates and adds a join to the query. * * <code> * $qb = $conn->getQueryBuilder() * ->select('u.name') * ->from('users', 'u') * ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance. */ public function join($fromAlias, $join, $alias, $condition = null) { $this->queryBuilder->join($fromAlias, $this->helper->quoteColumnName($join), $alias, $condition); return $this; }
/** * Joins the page table and limits created_by to currently logged in user. * * @param QueryBuilder $q */ public function limitQueryToCreator(QueryBuilder &$q) { $q->join('t', MAUTIC_TABLE_PREFIX . 'sms_messages', 's', 's.id = t.sms_id')->andWhere('s.created_by = :userId')->setParameter('userId', $this->user->getId()); }
/** * Joins the email table and limits created_by to currently logged in user. * * @param QueryBuilder $q */ public function limitQueryToCreator(QueryBuilder $q) { $q->join('t', MAUTIC_TABLE_PREFIX . 'focus', 'm', 'e.id = t.focus_id')->andWhere('m.created_by = :userId')->setParameter('userId', $this->userHelper->getUser()->getId()); }