Construtor de Queries¶

Selecionando Linhas de uma Tabela¶

use Cake\ORM\TableRegistry;

// Anterior a 3.6.0
$query = TableRegistry::get('Articles')->find();

$query = TableRegistry::getTableLocator()->get('Articles')->find();

foreach ($query as $article) {
    debug($article->title);
}

Para os exemplos restantes, suponha que $articles seja um Table. Quando dentro de controladores, você pode usar $this->Articles em vez de $articles.

Quase todos os métodos em um objeto Query retornam a mesma consulta, isso significa que os objetos Query são preguiçosos e não serão executados a menos que você solicite:

$query->where(['id' => 1]); // Retornar o mesmo objeto de consulta
$query->order(['title' => 'DESC']); // Ainda o mesmo objeto, nenhum SQL executado

É claro que você pode encadear os métodos que você chama nos objetos de consulta:

$query = $articles
    ->find()
    ->select(['id', 'name'])
    ->where(['id !=' => 1])
    ->order(['created' => 'DESC']);

foreach ($query as $article) {
    debug($article->created);
}

Se você tentar chamar debug() em um objeto Query, verá seu estado interno e o SQL que será executado no banco de dados:

debug($articles->find()->where(['id' => 1]));

// Saídas
// ...
// 'sql' => 'SELECT * FROM articles where id = ?'
// ...

Você pode executar uma consulta diretamente sem precisar usar foreach nela. A maneira mais fácil é chamar os métodos all() ou toList():

$resultsIteratorObject = $articles
    ->find()
    ->where(['id >' => 1])
    ->all();

foreach ($resultsIteratorObject as $article) {
    debug($article->id);
}

$resultsArray = $articles
    ->find()
    ->where(['id >' => 1])
    ->toList();

foreach ($resultsArray as $article) {
    debug($article->id);
}

debug($resultsArray[0]->title);

No exemplo acima, $resultsIteratorObject será uma instância de Cake\ORM\ResultSet, um objeto no qual você pode iterar e aplicar vários métodos de extração e deslocamento.

Freqüentemente, não há necessidade de chamar all(), você pode simplesmente iterar o objeto Query para obter seus resultados. Objetos de consulta também podem ser usados diretamente como objeto de resultado; tentar iterar a consulta, chamando toList() ou alguns dos métodos herdados de Collection, resultará na execução da consulta e nos resultados retornados a você.

Selecionando uma Única Linha de uma Tabela¶

Você pode usar o método first() para obter o primeiro resultado na consulta:

$article = $articles
    ->find()
    ->where(['id' => 1])
    ->first();

debug($article->title);

Obtendo uma Lista de Valores de uma Coluna¶

// Use o método extract() da biblioteca de coleções
// Isso executa a consulta também
$allTitles = $articles->find()->extract('title');

foreach ($allTitles as $title) {
    echo $title;
}

Você também pode obter uma lista de valores-chave de um resultado da consulta:

$list = $articles->find('list');

foreach ($list as $id => $title) {
    echo "$id : $title"
}

Para obter mais informações sobre como personalizar os campos usados para preencher a lista, consulte seção Encontrando Chaves/Pares de Valores.

As Consultas são Objetos de Coleção¶

Depois de se familiarizar com os métodos do objeto Query, é altamente recomendável que você visite a seção Coleção para melhorar suas habilidades em percorrer os dados com eficiência. Em resumo, é importante lembrar que qualquer coisa que você possa chamar em um objeto Collection, você também pode fazer em um objeto Query:

// Use o método combine() da biblioteca de coleções,
// isto é equivalente a find('list')
$keyValueList = $articles->find()->combine('id', 'title');

// Um exemplo avançado
$results = $articles->find()
    ->where(['id >' => 1])
    ->order(['title' => 'DESC'])
    ->map(function ($row) { // map() é um método de coleção, ele executa a consulta
        $row->trimmedTitle = trim($row->title);

        return $row;
    })
    ->combine('id', 'trimmedTitle') // combine() é outro método de coleção
    ->toArray(); // Também um método dA biblioteca de coleções

foreach ($results as $id => $trimmedTitle) {
    echo "$id : $trimmedTitle";
}

As Consultas são Avaliadas Preguiçosamente¶

Objetos de consulta são avaliados preguiçosamente. Isso significa que uma consulta não é executada até que ocorra uma das seguintes coisas:

• A consulta é iterada com foreach().

• O método execute() da consulta é chamado. Isso retornará o objeto subjacente de instrução e deve ser usado com consultas de inserção/atualização/exclusão.

• O método first() da consulta é chamado. Isso retornará o primeiro resultado no conjunto construído por SELECT (ele adiciona LIMIT 1 à consulta).

• O método all() da consulta é chamado. Isso retornará o conjunto de resultados e   só pode ser usado com instruções SELECT.

• O método toList() ou toArray() da consulta é chamado.

Até que uma dessas condições seja atendida, a consulta pode ser modificada sem que SQL adicional seja enviado ao banco de dados. Isso também significa que, se uma consulta não tiver sido realizada, nenhum SQL é enviado ao banco de dados. Uma vez executada, modificar e reavaliar uma consulta resultará na execução de SQL adicional.

Se você quiser dar uma olhada no que o SQL CakePHP está gerando, você pode ativar o banco de dados query logging.

Selecionando Campos Específicos¶

Por padrão, uma consulta seleciona todos os campos de uma tabela, a exceção é quando você chama a função select() e passa determinados campos:

// Selecione apenas ID e título da tabela de artigos
$articles->find()->select(['id', 'title']);

Se você ainda deseja selecionar todos os campos de uma tabela depois de chamar select($fields), pode passar a instância da tabela para select() para esse propósito:

// Seleciona todos os campos da tabela de artigos,
// incluindo um campo slug calculado.
$query = $articlesTable->find();
$query
    ->select(['slug' => $query->func()->concat(['title' => 'identifier', '-', 'id' => 'identifier'])])
    ->select($articlesTable); // Select all fields from articles

Se você desejar selecionar todos os campos, exceto alguns, em uma tabela, pode usar selectAllExcept():

$query = $articlesTable->find();

// Obtenha todos os campos, exceto o campo publicado.
$query->selectAllExcept($articlesTable, ['published']);

Você também pode passar um objeto Association ao trabalhar com associações embutidas.

Usando Funções SQL¶

O ORM do CakePHP oferece abstração para algumas funções SQL comumente usadas. O uso da abstração permite que o ORM selecione a implementação específica da plataforma da função desejada. Por exemplo, concat é implementado de maneira diferente no MySQL, PostgreSQL e SQL Server. O uso da abstração permite que seu código seja portátil:

// Resultados em SELECT COUNT(*) count FROM...
$query = $articles->find();
$query->select(['count' => $query->func()->count('*')]);

Várias funções comumente usadas podem ser criadas com o método func():

rand()

Gere um valor aleatório entre 0 e 1 via SQL.

sum()

Calcular uma soma. Assume que argumentos são valores literais.

avg()

Calcule uma média. Assume que argumentos são valores literais.

min()

Calcule o mínimo de uma coluna. Assume que argumentos são valores literais.

max()

Calcule o máximo de uma coluna. Assume que argumentos são valores literais.

count()

Calcule a contagem. Assume que argumentos são valores literais.

concat()

Concatene dois valores juntos. Assume que os argumentos são parâmetros vinculados.

coalesce()

Agrupar valores. Assume que os argumentos são parâmetros vinculados.

dateDiff()

Obtenha a diferença entre duas datas/horas. Assume que os argumentos são parâmetros vinculados.

now()

O padrão é retornar data e hora, mas aceita “time” ou “date” para retornar apenas esses valores.

extract()

Retorna a parte da data especificada da expressão SQL.

dateAdd()

Adicione a unidade de tempo à expressão de data.

dayOfWeek()

Retorna uma FunctionExpression representando uma chamada para a função SQL WEEKDAY.

$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
    'Articles.title' => 'identifier',
    ' - CAT: ',
    'Categories.name' => 'identifier',
    ' - Age: ',
    $query->func()->dateDiff(
        'NOW()' => 'literal',
        'Articles.created' => 'identifier'
    )
]);
$query->select(['link_title' => $concat]);

SELECT CONCAT(
    Articles.title,
    :c0,
    Categories.name,
    :c1,
    (DATEDIFF(NOW(), Articles.created))
) FROM articles;

$query = $articles->find();
$year = $query->func()->year([
    'created' => 'identifier'
]);
$time = $query->func()->date_format([
    'created' => 'identifier',
    "'%H:%i'" => 'literal'
]);
$query->select([
    'yearCreated' => $year,
    'timeCreated' => $time
]);

SELECT YEAR(created) as yearCreated,
       DATE_FORMAT(created, '%H:%i') as timeCreated
FROM articles;

Agregadores - Group e Having¶

Ao usar funções agregadas como count e sum, você pode usar as cláusulas group by e having:

$query = $articles->find();
$query->select([
    'count' => $query->func()->count('view_count'),
    'published_date' => 'DATE(created)'
])
->group('published_date')
->having(['count >' => 3]);

Declarações de Caso¶

O ORM também oferece a expressão SQL case. A expressão case permite implementar a lógica if... then... else dentro do seu SQL. Isso pode ser útil para gerar relatórios sobre dados nos quais você precisa somar ou contar condicionalmente ou onde precisa de dados específicos com base em uma condição.

Se desejassemos saber quantos artigos publicados estão em nosso banco de dados, poderíamos usar o seguinte SQL:

SELECT
COUNT(CASE WHEN published = 'Y' THEN 1 END) AS number_published,
COUNT(CASE WHEN published = 'N' THEN 1 END) AS number_unpublished
FROM articles

Para fazer isso com o construtor de consultas, usaríamos o seguinte código:

$query = $articles->find();
$publishedCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['published' => 'Y']),
        1,
        'integer'
    );
$unpublishedCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['published' => 'N']),
        1,
        'integer'
    );

$query->select([
    'number_published' => $query->func()->count($publishedCase),
    'number_unpublished' => $query->func()->count($unpublishedCase)
]);

A função addCase também pode encadear várias instruções para criar if .. then .. [elseif .. then ..] [.. else] lógica dentro de seu SQL.

Se quisermos classificar as cidades em SMALL, MEDIUM ou LARGE, com base no tamanho da população, poderíamos fazer o seguinte:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->addCase(
            [
                $q->newExpr()->lt('population', 100000),
                $q->newExpr()->between('population', 100000, 999000),
                $q->newExpr()->gte('population', 999001),
            ],
            ['SMALL',  'MEDIUM', 'LARGE'], # valores que correspondem às condições
            ['string', 'string', 'string'] # tipo de cada valor
        );
    });
# WHERE CASE
#   WHEN population < 100000 THEN 'SMALL'
#   WHEN population BETWEEN 100000 AND 999000 THEN 'MEDIUM'
#   WHEN population >= 999001 THEN 'LARGE'
#   END

Sempre que houver menos condições de casos que valores, addCase produzirá automaticamente uma declaração if .. then .. else:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->addCase(
            [
                $q->newExpr()->eq('population', 0),
            ],
            ['DESERTED', 'INHABITED'], # values matching conditions
            ['string', 'string'] # type of each value
        );
    });
# WHERE CASE
#   WHEN population = 0 THEN 'DESERTED' ELSE 'INHABITED' END

Obtendo Matrizes em Vez de Entidades¶

Embora os ORMs e os conjuntos de resultados de objetos sejam poderosos, às vezes a criação de entidades é desnecessária. Por exemplo, ao acessar dados agregados, a construção de uma Entidade pode não fazer sentido. O processo de conversão dos resultados do banco de dados em entidades é chamado de hidratação. Se você deseja desativar esse processo, você pode fazer isso:

$query = $articles->find();
$query->enableHydration(false); // Resultados como matrizes em vez de entidades
$result = $query->toList(); // Execute a consulta e retorne a matriz

Depois de executar essas linhas, seu resultado deve ser semelhante a este:

[
    ['id' => 1, 'title' => 'First Article', 'body' => 'Article 1 body' ...],
    ['id' => 2, 'title' => 'Second Article', 'body' => 'Article 2 body' ...],
    ...
]

Adicionando Campos Calculados¶

Após suas consultas, talvez seja necessário fazer um pós-processamento. Se você precisar adicionar alguns campos calculados ou dados derivados, poderá usar o método formatResults(). Essa é uma maneira leve de mapear os conjuntos de resultados. Se você precisar de mais controle sobre o processo, ou desejar reduzir os resultados, use o recurso Map/Reduce. Se você estava consultando uma lista de pessoas, poderia calcular a idade delas com um formatador de resultados:

// Supondo que construímos os campos, condições e contenções.
$query->formatResults(function (\Cake\Collection\CollectionInterface $results) {
    return $results->map(function ($row) {
        $row['age'] = $row['birth_date']->diff(new \DateTime)->y;

        return $row;
    });
});

Como você pode ver no exemplo acima, a formatação de retornos de chamada receberá um ResultSetDecorator como seu primeiro argumento. O segundo argumento será a instância de consulta à qual o formatador foi anexado. O argumento $results pode ser percorrido e modificado conforme necessário.

Os formatadores de resultados são necessários para retornar um objeto iterador, que será usado como o valor de retorno para a consulta. As funções do formatador são aplicadas após a execução de todas as rotinas de Mapa/Redução. Os formatadores de resultados também podem ser aplicados a partir de associações contidas. O CakePHP garantirá que seus formatadores tenham um escopo adequado. Por exemplo, fazer o seguinte funcionaria conforme o esperado:

// Em um método na tabela Artigos
$query->contain(['Authors' => function ($q) {
    return $q->formatResults(function (\Cake\Collection\CollectionInterface $authors) {
        return $authors->map(function ($author) {
            $author['age'] = $author['birth_date']->diff(new \DateTime)->y;

            return $author;
        });
    });
}]);

// Obtem os resultados
$results = $query->all();

// Saída 29
echo $results->first()->author->age;

Como visto acima, os formatadores anexados aos criadores de consultas associados têm o escopo definido para operar apenas nos dados da associação. O CakePHP garantirá que os valores computados sejam inseridos na entidade correta.

Usando Identificadores em Expressões¶

Quando você precisar fazer referência a uma coluna ou identificador SQL em suas consultas, poderá usar o método identifier():

$query = $countries->find();
$query->select([
        'year' => $query->func()->year([$query->identifier('created')])
    ])
    ->where(function ($exp, $query) {
        return $exp->gt('population', 100000);
    });

Criando Cláusulas IN Automaticamente¶

Ao criar consultas usando o ORM, geralmente você não precisará indicar os tipos de dados das colunas com as quais está interagindo, pois o CakePHP pode inferir os tipos com base nos dados do esquema. Se em suas consultas você deseja que o CakePHP converta automaticamente a igualdade em comparações IN, será necessário indicar o tipo de dados da coluna:

$query = $articles->find()
    ->where(['id' => $ids], ['id' => 'integer[]']);

// Ou inclua IN para converter automaticamente em uma matriz.
$query = $articles->find()
    ->where(['id IN' => $ids]);

O exemplo acima criará automaticamente id IN (...) em vez de id = ?. Isso pode ser útil quando você não sabe se receberá um valor escalar ou matriz de parâmetros. O sufixo [] em qualquer nome de tipo de dados indica para o construtor de consultas que você deseja que os dados sejam tratados como uma matriz. Se os dados não forem uma matriz, eles serão convertidos em uma matriz. Depois disso, cada valor na matriz será convertido usando o type system. Isso funciona com tipos complexos também. Por exemplo, você pode pegar uma lista de objetos DateTime usando:

$query = $articles->find()
    ->where(['post_date' => $dates], ['post_date' => 'date[]']);

Criação Automática de IS NULL¶

Quando se espera que um valor de condição seja null ou qualquer outro valor, você pode usar o operador IS para criar automaticamente a expressão correta:

$query = $categories->find()
    ->where(['parent_id IS' => $parentId]);

O exemplo acima criará parent_id` =: c1 ou parent_id IS NULL, dependendo do tipo de $parentId

Criação Automática de IS NOT NULL¶

Quando se espera que um valor de condição não seja null ou qualquer outro valor, você pode usar o operador IS NOT para criar automaticamente a expressão correta:

$query = $categories->find()
    ->where(['parent_id IS NOT' => $parentId]);

O exemplo acima criará parent_id` != :c1 ou parent_id IS NOT NULL, dependendo do tipo de $parentId

Expressões Nativas¶

Quando você não pode construir o SQL necessário usando o construtor de consultas, pode usar objetos de expressão para adicionar trechos de SQL às suas consultas:

$query = $articles->find();
$expr = $query->newExpr()->add('1 + 1');
$query->select(['two' => $expr]);

Expression objetos podem ser usados com qualquer método do construtor de consultas, como where(), limit(), group(), select() e muitos outros métodos.

Retornando a Contagem Total de Registros¶

Usando um único objeto de consulta, é possível obter o número total de linhas encontradas para um conjunto de condições:

$total = $articles->find()->where(['is_active' => true])->count();

O método count() ignorará as cláusulas limit, offset e page, portanto, o seguinte retornará o mesmo resultado:

$total = $articles->find()->where(['is_active' => true])->limit(10)->count();

Isso é útil quando você precisa conhecer o tamanho total do conjunto de resultados com antecedência, sem precisar construir outro objeto Query. Da mesma forma, todas as rotinas de formatação e redução de mapa são ignoradas ao usar o método count().

Além disso, é possível retornar a contagem total de uma consulta contendo cláusulas de grupo sem precisar reescrever a consulta de nenhuma maneira. Por exemplo, considere esta consulta para recuperar IDs de artigos e contagem de seus comentários:

$query = $articles->find();
$query->select(['Articles.id', $query->func()->count('Comments.id')])
    ->matching('Comments')
    ->group(['Articles.id']);
$total = $query->count();

Após a contagem, a consulta ainda pode ser usada para buscar os registros associados:

$list = $query->all();

Às vezes, convém fornecer um método alternativo para contar o total de registros de uma consulta. Um caso de uso comum para isso é fornecer um valor em cache ou uma estimativa do total de linhas ou alterar a consulta para remover partes desnecessariamente caras, como left joins. Isso se torna particularmente útil ao usar o sistema de paginação do CakePHP que chama o método count():

$query = $query->where(['is_active' => true])->counter(function ($query) {
    return 100000;
});
$query->count(); // Retorna 100000

No exemplo acima, quando o componente de paginação chamar o método count, ele receberá o número estimado de linhas codificadas

Cache de Resultados Carregados¶

Ao buscar entidades que não mudam com frequência, convém armazenar em cache os resultados. A classe Query torna isso simples:

$query->cache('recent_articles');

Ativará o cache no conjunto de resultados da consulta. Se apenas um argumento for fornecido para cache(), a configuração de cache “padrão” será usada. Você pode controlar qual configuração de armazenamento em cache é usada com o segundo parâmetro:

// Nome da configuração.
$query->cache('recent_articles', 'dbResults');

// Instância de CacheEngine
$query->cache('recent_articles', $memcache);

Além de suportar chaves estáticas, o método cache() aceita uma função para gerar a chave. A função que você fornecer receberá a consulta como argumento. Você pode ler aspectos da consulta para gerar dinamicamente a chave de cache:

// Gere uma chave com base em uma soma de verificação simples
// da cláusula where da consulta
$query->cache(function ($q) {
    return 'articles-' . md5(serialize($q->clause('where')));
});

O método de cache simplifica a adição de resultados em cache aos seus finders personalizados ou através dos ouvintes de eventos.

Quando os resultados de uma consulta em cache são buscados, acontece o seguinte:

1. Se a consulta tiver resultados definidos, eles serão retornados.

2. A chave do cache será resolvida e os dados do cache serão lidos. Se os dados do cache não estiverem vazios, esses resultados serão retornados.

3. Se o cache falhar, a consulta será executada, o evento Model.beforeFind será acionado e um novo ResultSet será criado. Este ResultSet será gravado no cache e retornado.

Passing Conditions to Contain¶

When using contain() you are able to restrict the data returned by the associations and filter them by conditions:

// In a controller or table method.

$query = $articles->find()->contain([
    'Comments' => function ($q) {
       return $q
            ->select(['body', 'author_id'])
            ->where(['Comments.approved' => true]);
    }
]);

This also works for pagination at the Controller level:

$this->paginate['contain'] = [
    'Comments' => function (\Cake\ORM\Query $query) {
        return $query->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
    }
];

It is also possible to restrict deeply-nested associations using the dot notation:

$query = $articles->find()->contain([
    'Comments',
    'Authors.Profiles' => function ($q) {
        return $q->where(['Profiles.is_published' => true]);
    }
]);

If you have defined some custom finder methods in your associated table, you can use them inside contain():

// Bring all articles, but only bring the comments that are approved and
// popular.
$query = $articles->find()->contain([
    'Comments' => function ($q) {
       return $q->find('approved')->find('popular');
    }
]);

If you need full control over the query that is generated, you can tell contain() to not append the foreignKey constraints to the generated query. In that case you should use an array passing foreignKey and queryBuilder:

$query = $articles->find()->contain([
    'Authors' => [
        'foreignKey' => false,
        'queryBuilder' => function ($q) {
            return $q->where(/* ... */); // Full conditions for filtering
        }
    ]
]);

If you have limited the fields you are loading with select() but also want to load fields off of contained associations, you can pass the association object to select():

// Select id & title from articles, but all fields off of Users.
$query = $articles->find()
    ->select(['id', 'title'])
    ->select($articlesTable->Users)
    ->contain(['Users']);

Alternatively, if you have multiple associations, you can use autoFields():

// Select id & title from articles, but all fields off of Users, Comments
// and Tags.
$query->select(['id', 'title'])
    ->contain(['Comments', 'Tags'])
    ->autoFields(true)
    ->contain(['Users' => function($q) {
        return $q->autoFields(true);
    }]);

Sorting Contained Associations¶

When loading HasMany and BelongsToMany associations, you can use the sort option to sort the data in those associations:

$query->contain([
    'Comments' => [
        'sort' => ['Comment.created' => 'DESC']
    ]
]);

Filtrando por Dados Aassociados¶

A fairly common query case with associations is finding records “matching” specific associated data. For example if you have “Articles belongsToMany Tags” you will probably want to find Articles that have the CakePHP tag. This is extremely simple to do with the ORM in CakePHP:

// In a controller or table method.

$query = $articles->find();
$query->matching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

You can apply this strategy to HasMany associations as well. For example if “Authors HasMany Articles”, you could find all the authors with recently published articles using the following:

$query = $authors->find();
$query->matching('Articles', function ($q) {
    return $q->where(['Articles.created >=' => new DateTime('-10 days')]);
});

Filtering by deep associations is surprisingly easy, and the syntax should be already familiar to you:

// In a controller or table method.
$query = $products->find()->matching(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

// Bring unique articles that were commented by 'markstory' using passed variable
// Dotted matching paths should be used over nested matching() calls
$username = 'markstory';
$query = $articles->find()->matching('Comments.Users', function ($q) use ($username) {
    return $q->where(['username' => $username]);
});

The data from the association that is “matched” will be available on the _matchingData property of entities. If you both match and contain the same association, you can expect to get both the _matchingData and standard association properties in your results.

Using innerJoinWith¶

Using the matching() function, as we saw already, will create an INNER JOIN with the specified association and will also load the fields into the result set.

There may be cases where you want to use matching() but are not interested in loading the fields into the result set. For this purpose, you can use innerJoinWith():

$query = $articles->find();
$query->innerJoinWith('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

The innerJoinWith() method works the same as matching(), that means that you can use dot notation to join deeply nested associations:

$query = $products->find()->innerJoinWith(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

Again, the only difference is that no additional columns will be added to the result set, and no _matchingData property will be set.

Using notMatching¶

The opposite of matching() is notMatching(). This function will change the query so that it filters results that have no relation to the specified association:

// In a controller or table method.

$query = $articlesTable
    ->find()
    ->notMatching('Tags', function ($q) {
        return $q->where(['Tags.name' => 'boring']);
    });

The above example will find all articles that were not tagged with the word boring. You can apply this method to HasMany associations as well. You could, for example, find all the authors with no published articles in the last 10 days:

$query = $authorsTable
    ->find()
    ->notMatching('Articles', function ($q) {
        return $q->where(['Articles.created >=' => new \DateTime('-10 days')]);
    });

It is also possible to use this method for filtering out records not matching deep associations. For example, you could find articles that have not been commented on by a certain user:

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    });

Since articles with no comments at all also satisfy the condition above, you may want to combine matching() and notMatching() in the same query. The following example will find articles having at least one comment, but not commented by a certain user:

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    })
    ->matching('Comments');

Keep in mind that contrary to the matching() function, notMatching() will not add any data to the _matchingData property in the results.

Using leftJoinWith¶

On certain occasions you may want to calculate a result based on an association, without having to load all the records for it. For example, if you wanted to load the total number of comments an article has along with all the article data, you can use the leftJoinWith() function:

$query = $articlesTable->find();
$query->select(['total_comments' => $query->func()->count('Comments.id')])
    ->leftJoinWith('Comments')
    ->group(['Articles.id'])
    ->autoFields(true);

The results for the above query will contain the article data and the total_comments property for each of them.

leftJoinWith() can also be used with deeply nested associations. This is useful, for example, for bringing the count of articles tagged with a certain word, per author:

$query = $authorsTable
    ->find()
    ->select(['total_articles' => $query->func()->count('Articles.id')])
    ->leftJoinWith('Articles.Tags', function ($q) {
        return $q->where(['Tags.name' => 'awesome']);
    })
    ->group(['Authors.id'])
    ->autoFields(true);

This function will not load any columns from the specified associations into the result set.

Adicionando Junções¶

Além de carregar dados relacionados com contains(), você também pode adicionar junções adicionais com o construtor de consultas:

$query = $articles->find()
    ->join([
        'table' => 'comments',
        'alias' => 'c',
        'type' => 'LEFT',
        'conditions' => 'c.article_id = articles.id',
    ]);

Você pode anexar várias junções ao mesmo tempo passando uma matriz associativa com várias junções:

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => 'c.article_id = articles.id',
        ],
        'u' => [
            'table' => 'users',
            'type' => 'INNER',
            'conditions' => 'u.id = articles.user_id',
        ]
    ]);

Como visto acima, ao adicionar junções, o alias pode ser a chave da matriz externa. As condições de junção também podem ser expressas como uma matriz de condições:

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.created >' => new DateTime('-5 days'),
                'c.moderated' => true,
                'c.article_id = articles.id'
            ]
        ],
    ], ['c.created' => 'datetime', 'c.moderated' => 'boolean']);

Ao criar junções manualmente e usar condições baseadas em matriz, é necessário fornecer os tipos de dados para cada coluna nas condições de junção. Ao fornecer tipos de dados para as condições de junção, o ORM pode converter corretamente os tipos de dados em SQL. Além de join(), você pode usar rightJoin(), leftJoin() e innerJoin() para criar junções:

// Join com um alias e condições de string
$query = $articles->find();
$query->leftJoin(
    ['Authors' => 'authors'],
    ['Authors.id = Articles.author_id']);

// Join com um alias, matriz de condições e tipos
$query = $articles->find();
$query->innerJoin(
    ['Authors' => 'authors'],
    [
    'Authors.promoted' => true,
    'Authors.created' => new DateTime('-5 days'),
    'Authors.id = Articles.author_id'
    ],
    ['Authors.promoted' => 'boolean', 'Authors.created' => 'datetime']);

Deve-se observar que, se você definir a opção quoteIdentifiers como true ao definir sua Conexão, as condições de junção entre os campos da tabela deverão ser definidas da seguinte forma:

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.article_id' => new \Cake\Database\Expression\IdentifierExpression('articles.id')
            ]
        ],
    ]);

Isso garante que todos os seus identificadores sejam citados em toda a consulta, evitando erros com alguns drivers de banco de dados (notavelmente no PostgreSQL)

Valores de Ligação¶

É possível proteger contra muitas situações inseguras usando ligações. Semelhante a vinculando valores a instruções preparadas, os valores podem ser vinculados a consultas usando o método Cake\Database\Query::bind()

O exemplo a seguir seria uma variante segura do exemplo inseguro, propenso a injeção de SQL, dado acima:

$query
    ->where([
        'MATCH (comment) AGAINST (:userData)',
        'created < NOW() - :moreUserData'
    ])
    ->bind(':userData', $userData, 'string')
    ->bind(':moreUserData', $moreUserData, 'datetime');

Unions¶

As Unions são criadas compondo uma ou mais consultas selecionadas juntas:

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->union($inReview);

Você pode criar consultas UNION ALL usando o método unionAll():

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->unionAll($inReview);

Subconsultas¶

As subconsultas são um recurso poderoso nos bancos de dados relacionais e sua criação no CakePHP é bastante intuitiva. Ao compor consultas em conjunto, você pode criar subconsultas:

// Antes da versão 3.6.0, use o association().
$matchingComment = $articles->getAssociation('Comments')->find()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment LIKE' => '%CakePHP%']);

$query = $articles->find()
    ->where(['id IN' => $matchingComment]);

Subqueries are accepted anywhere a query expression can be used. For example, in the select() and join() methods.

Subconsultas são aceitas em qualquer lugar em que uma expressão de consulta possa ser usada. Por exemplo, nos métodos select() e join()

Adicionando Instruções de Bloqueio¶

A maioria dos fornecedores de bancos de dados relacionais suporta a remoção de bloqueios ao executar operações selecionadas. Você pode usar o método epilog() para este:

// Em MySQL
$query->epilog('FOR UPDATE');

O método epilog() permite anexar SQL bruto ao final das consultas. Você nunca deve colocar dados brutos do usuário em epilog()

Executando Consultas Complexas¶

Embora o construtor de consultas facilite a criação da maioria das consultas, consultas muito complexas podem ser entediantes e complicadas. Você pode executar o SQL desejado diretamente.

A execução direta do SQL permite ajustar a consulta que será executada. No entanto, isso não permite que você use contains ou outros recursos ORM de nível superior.