$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());
}
