I’m running into some trouble getting the syntax of the queryBulilder()->expr() helper methods right.
Problem I’m trying to solve:
I’m addressing this issue by adding an additional filter onto the PropertyAdapter's buildQuery() method so that if a 'site' parameter is passed to the search api (e.g. $apiManager()->search('property', ['site'=>$site_id])) then the result will return only the properties used by the given site’s items.
What I have done to solve it
I have a query in the PropertyAdapter::buildQuery() to get the item id’s from item_site table that I then using that to limit the properties
if (!empty($query['used'])) {
//. . .
//existing code to limit to properties to those that show up in values table
//. . .
//my solution to further limit by site
if (!empty($query['site']) && is_numeric($query['site'])) {
$dql = "SELECT i.id FROM Omeka\Entity\Site s JOIN s.items i WHERE s.id = ?1";
$items = $this->getEntityManager()->createQuery($dql)
->setParameter(1, $query['site'])
->getResult();
$qb->andWhere($valuesAlias . '.resource IN (:ids)')
->setParameter('ids', $items);
}
}
Issue I’m experiencing
While this method works, it seems like the preferred pattern is to use the Expression helper methods, and according to the documentation that may also help optimize the query internally. However, I’ve been unable to recreate a query using expr(). I didn’t find the Doctrine documentation particularly helpful for this application, but I might have missed something. Can anyone point me to a resource that might help, or provide any other insights on the best way to implement this solution?
What of this are you trying to replace with expr() expressions?
It’s used for things like the IN in your where clause but not to replace an entire select.
More broadly, to limit by properties used in a site, what you probably want to do is not do a separate query, but just add joins to the items and sites tables to the existing base resource query.
Thanks @jflatnes. The way I was imagining it with expressions was a subquery inside an expression expr()->in($valuesAlias . '.resource', $subquery) . But yeah, I’ll go with the two joins. I had first tried that but wasn’t getting the results I was expecting. I was basically getting the exact same results as just joining on values
Which, when I logged the query that was executed on the db, it wasn’t actually using the item_site join table in the query. Do I need to more explicitly state how to manage the join relationship here?
You can do a subquery too if you want to do it that way: to make the inner query you’d either write it all out or make a new query builder to build the subquery and then pass the SQL from that to the where method.
For joining, you do need to specify more than you’re doing there: you have to specify either the join condition or the relationship you’re using for the join (for example, you joined to s.items in your manual DQL version, which was specifying the relationship).
Awesome, thanks a lot. I got the subquery method working, but I guess I’d like to use the joins for the sake of consistency. But when I try adding conditions this way:
I get an a semantic error: [Semantical Error] line 0, col 193 near 'items WHERE omeka_1.id': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.
Using $siteAlias . ".items" must not be the way to go, but I’m not sure how else to do it.
Similarly to how you wrote it by hand in your subquery, if you want to join using a defined relationship, you just specify the relationship directly as what you’re joining to. Here’s an example of that kind of join. So having $siteAlias . ".items" as the first argument to the join method (and then no 3rd or 4th argument) is what’s the basic equivalent here of the way you were doing it when writing by hand.