When a condition value is expected to be null or any other value, you can use the IS operator to automatically create the correct expression:. When a condition value is expected not to be null or any other value, you can use the IS NOT operator to automatically create the correct expression:. When you cannot construct the SQL you need using the query builder, you can use expression objects to add snippets of SQL to your queries:. Expression objects can be used with any query builder methods like where , limit , group , select and many other methods.
Using expression objects leaves you vulnerable to SQL injection. You should never use untrusted data into expressions. There are a few ways of doing this:. You can use any of the collection methods on your query objects to pre-process or transform the results:. You can use first or firstOrFail to retrieve a single record. Using a single query object, it is possible to obtain the total number of rows found for a set of conditions:. The count method will ignore the limit , offset and page clauses, thus the following will return the same result:.
This is useful when you need to know the total result set size in advance, without having to construct another Query object. Likewise, all result formatting and map-reduce routines are ignored when using the count method.
Moreover, it is possible to return the total count for a query containing group by clauses without having to rewrite the query in any way. For example, consider this query for retrieving article ids and their comments count:. Sometimes, you may want to provide an alternate method for counting the total records of a query. One common use case for this is providing a cached value or an estimate of the total rows, or to alter the query to remove expensive unneeded parts such as left joins.
This becomes particularly handy when using the CakePHP built-in pagination system which calls the count method:. In the example above, when the pagination component calls the count method, it will receive the estimated hard-coded number of rows.
The Query class makes this simple:. You can control which caching configuration is used with the second parameter:. In addition to supporting static keys, the cache method accepts a function to generate the key. The function you give it will receive the query as an argument.
You can then read aspects of the query to dynamically generate the cache key:. The cache method makes it simple to add cached results to your custom finders or through event listeners. The Model. The cache key will be resolved and cache data will be read. If the cache data is not empty, those results will be returned.
If the cache misses, the query will be executed and a new ResultSet will be created. This ResultSet will be written to the cache and returned. The builder can help you retrieve data from multiple tables at the same time with the minimum amount of queries possible. To be able to fetch associated data, you first need to setup associations between the tables as described in the Associations - Linking Tables Together section. This technique of combining queries to fetch associated data from other tables is called eager loading.
Eager loading helps avoid many of the potential performance problems surrounding lazy-loading in an ORM. The queries generated by eager loading can better leverage joins, allowing more efficient queries to be made. The above will load the related author and comments for each article in the result set. You can load nested associations using nested arrays to define the associations to be loaded:.
You can select fields from all associations with multiple easy contain statements:. If you need to reset the containments on a query you can set the second argument to true :. When using contain you are able to restrict the data returned by the associations and filter them by conditions. When you limit the fields that are fetched from an association, you must ensure that the foreign key columns are selected. Failing to select foreign key fields will cause associated data to not be present in the final result.
To only get authors with a published profile use matching. If you have defined custom finders in your associations, you can use them inside contain :. For BelongsTo and HasOne associations only the where and select clauses are used when loading the associated records. For the rest of the association types you can use every clause that the query object provides. 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 :. 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 :. Alternatively, if you have multiple associations, you can use enableAutoFields :.
When loading HasMany and BelongsToMany associations, you can use the sort option to sort the data in those associations:. You can apply this strategy to HasMany associations as well. Filtering by deep associations is surprisingly easy, and the syntax should be already familiar to you:. This might be the case, for example, when the same users comments more than once on a single article.
Sometimes you need to match specific associated data but without actually loading the matching records like matching. You can combine innerJoinWith and contain with the same association when you want to match specific records and load the associated data together. The example below matches Articles that have specific Tags and loads the same Tags:.
If you use innerJoinWith and want to select fields from that association, you need to use an alias for the field:. This is an edge case from matching not knowing you manually selected the field. You should not combine innerJoinWith and matching with the same association. 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:.
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:. 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:. 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:. 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:.
That makes no sense to me. In what use case would one ever want to produce such a query? It does not throw a DB exception but is an invalid query that will never yield any useful result. The obvious one: Throw an exception for invalid queries instead of silently doing nothing creates a false sense of correctness here. Go back to the very intuitive and IMO most correct way of using 2.
Especially for the positive case IS I do not see any harm here - especially compared to the current bugs we have due to the silent query fault. That said I am fine with adding extra methods that would provide this. But I honestly do not think those should be necessary. Cake ORM should go back to the dev friendly way of converting this to what the DB needs without having to force the developer to add extras everywhere. This is not feasable. Let's please fix this now. To complete Solution B, also the negatation should then work as expected or throw meaningful exception as A suggests :.
Are there any issues of doing this properly inside the ORM? Or do we have to go with the exception because certain use cases such as nested 'NOT' conditions might fail here? This becomes necessary IMO if we start throwing exceptions - for the other case it would be a useful convenience here, as the other one would never result valid results either. But I can see why some people would not want too much magic here going on based on schema and instead probably prefer some runtime exception or more manual thought put into query building.
But with the related issue being fixed this one here will also become more clear hopefully. The text was updated successfully, but these errors were encountered:. However I don't like rewriting "foo! And I am not sure I like any kind of casting magic in where conditions at all. Sounds like the chances are high for head aches and security holes.
Maybe sometimes a column is nullable but also can take '', or it is not nullable but it can take '', or it is not nullable and neither is '' a legit value in terms of app domain validation Varchars will not be redefined. So something like this prior to the query will work:. Well, '' the empty string is not a valid value for an integer, ever. It just doesn't make any sense to ask if an integer field has the value of the empty string, it can only be NULL , 0, 1, -1, 2, -2, etc.
Moreover, even if you ask MySQL or force cake doing so to query your DB, there will never be any effect because of this condition maybe because of the other ones :.
How it can be corrected? You are not forced to use an associative array to define conditions.
0コメント