TYPO3 Core

Felix Oertel wrote:

Hi,

what exactly do you try to achieve with your statment, or better: what should the sql output of $query->contains('relation', array(1,2,3,4,5)) be? At first glance, the sql generated by this would not differ from those built with logicalOr or did I miss a way to simplify that statement?

regards, foertel

Maybe check also $query->in() as that could be exactly what you want.

regards,
Christian

Ok, given I have two tables linked by a M:M relation the logicalOr / array of constraints results in something like this:

SELECT tx_extension_domain_model_parent.*
FROM tx_extension_domain_model_parent
WHERE (
  tx_extension_domain_model_parent.uid
  IN (
    SELECT uid_local FROM tx_extension_domain_model_parent_child_mm WHERE uid_foreign =20
  )
  OR tx_extension_domain_model_parent.uid
  IN (
    SELECT uid_local FROM tx_extension_domain_model_parent_child_mm WHERE uid_foreign =19
  )
)
AND tx_extension_domain_model_parent.deleted =0
AND tx_extension_domain_model_parent.hidden =0
AND tx_extension_domain_model_parent.pid IN ( 27 )

So this results in N+1 queries being performed by the database.

I would like something like this to be the result when I pass in an array / collection of objects:

SELECT tx_extension_domain_model_parent.*
FROM tx_extension_domain_model_parent
WHERE tx_extension_domain_model_parent.uid IN (
  SELECT uid_local FROM tx_extension_domain_model_parent_child_mm WHERE uid_foreign IN (20,19)
)
AND tx_extension_domain_model_parent.deleted =0
AND tx_extension_domain_model_parent.hidden =0
AND tx_extension_domain_model_parent.pid IN ( 27 )

$query->in() is a partial solution, but it means that I need to (manually) reduce collection of model objects ( I already have ) into an array of say UIDs and then use $query->in('relation.uid', $array_of_uids). This seems redundant and counter-inituitive. Why would I need to manually perform these steps when even the choice of the constraint name (contains) implies querying a subset. The way it currently works a better name would be "is()", but that's only semantics.

Ok, I agree that should be handled better... Anyone else has some idea how to archive that with current code? Otherwise I would take it to make a patch for having arrays too in contains.

Morton Jonuschat wrote:

Ok, given I have two tables linked by a M:M relation the logicalOr / array of constraints results in something like this:
[...]

So this results in N+1 queries being performed by the database.

I would like something like this to be the result when I pass in an array / collection of objects:

[...]

$query->in() is a partial solution, but it means that I need to (manually) reduce collection of model objects ( I already have ) into an array of say UIDs and then use $query->in('relation.uid', $array_of_uids). This seems redundant and counter-inituitive. Why would I need to manually perform these steps when even the choice of the constraint name (contains) implies querying a subset. The way it currently works a better name would be "is()", but that's only semantics.

Hi everybody,

Ok, I agree that should be handled better... Anyone else has some idea how to archive that with current code? Otherwise I would take it to make a patch for having arrays too in contains.

I think just using an array in contains() is not really possible, as one cannot know which case is meant:

Example: $query->contains('relation', array(1,2,3,4,5));

• Find elements where relation is 1, or 2, or 3, ... (this is what you suggested)
• Find elements where there exists a relation to 1, AND to 2, AND to 3. (this is also a valid possibility in an M:N relation case).

... and Extbase can not resolve this ambiguity.

$query->in() is a partial solution, but it means that I need to (manually) reduce collection of model objects ( I already have ) into an array of say UIDs and then use $query->in('relation.uid', $array_of_uids).

Are you sure? I think you can directly use the model objects like $query->in('relation', $array_of_model_objects), and Extbase should figure out the UID stuff by itself.
If that is not supported, I'd rather suggest to include support for this, as we don't have such an ambiquity in this case.

Greets,
Sebastian

Ah, one more thing: When we change behavior here, we should open a ticket in the FLOW3 issue tracker, so Karsten can implement the corresponding functionality in the FLOW3 query language. Keeping these two parts in sync is crucial.

Sebastian Kurfuerst wrote:

Ah, one more thing: When we change behavior here, we should open a ticket in the FLOW3 issue tracker, so Karsten can implement the corresponding functionality in the FLOW3 query language. Keeping these two parts in sync is crucial.

Good point about the "AND" case.
Yes, something like that with $query->in() is what I hoped for... If its not there that would be a much cleaner way to go. This would do for the "OR" case, what about the "AND" case of relations?

1)

$query->in('relation', $array_of_model_objects)

2) I think there's a logical flaw in your "AND" case. I can't find a case where you will construct something where there is an "AND" condition on the child side of the relation with only a single attribute being queried. Any example I can come up with results in an empty result set. In my opinion the "AND" case happens on the parent side of the relation - which means it's an extra condition on the query and the current logicalAnd is the way to go. I think AND only comes into play if you are searching a parent object where there's a child with attr=value1 AND there's a child with attr=value2.

Morton Jonuschat wrote:

1) [...] didn't work for me, it was one the first things I tried. I needed to reduce the array_of_model_objects to uids and query the relation uid.

2) I think there's a logical flaw in your "AND" case. I can't find a case where you will construct something where there is an "AND" condition on the child side of the relation with only a single attribute being queried. Any example I can come up with results in an empty result set. In my opinion the "AND" case happens on the parent side of the relation - which means it's an extra condition on the query and the current logicalAnd is the way to go. I think AND only comes into play if you are searching a parent object where there's a child with attr=value1 AND there's a child with attr=value2.

What if you search for a parent that has relation to two childs, so something like that (just modified copy&paste, not optimized):

SELECT tx_extension_domain_model_parent.*
FROM tx_extension_domain_model_parent
WHERE (
  tx_extension_domain_model_parent.uid
  IN (
    SELECT uid_local FROM tx_extension_domain_model_parent_child_mm WHERE uid_foreign =20
  )
  AND tx_extension_domain_model_parent.uid
  IN (
    SELECT uid_local FROM tx_extension_domain_model_parent_child_mm WHERE uid_foreign =19
  )
)
AND tx_extension_domain_model_parent.deleted =0
AND tx_extension_domain_model_parent.hidden =0
AND tx_extension_domain_model_parent.pid IN ( 27 )

Should find you all parents that have childs with uid 20 AND 19. But for me this could be solved with logicalAnd.

Well, even with this example I can't see any advantage to having a "AND" Operator in the contains() constraint:

• The result is no different than using a simple logicalAnd, the programming using logicalAnd is more obvious (and better readable)
• There is no SQL command "optimizes" the N+1 Queries, so the "AND" would be some magic that just reduces a very little amount of typing for the programmer.

Contrasting the OR case

• The result is very different and much more optimized
• Most other ORMs handle it this way (OK, some have "whereIn" or something like that)
• There is an explicit SQL command for this use case.

I would be all too happy to settle for the solution of converting objects to ids you proposed 6 months ago. Beats doing it by hand any time.

@all review: Has there anything happened here ever since??

I don't think so. In current projects I work that way:

// create OR-Query for categories
foreach (GeneralUtility::trimExplode(',', $categories) as $category) {
    $categoryOrQuery[] = 'sys_category_record_mm.uid_local IN (\'' . (int) $category . '\')';
}

...

WHERE (' . implode(' OR ', $categoryOrQuery) . ')

Further keep in mind that Extbase does not respect col "tablenames" and "fieldname" in MM-tables (Yes I know that Frans is just working on that). So in that case it's better to use statements instead.

Stefan

everybody cool with that?

I'm cool with that, making it easier for the simple cases is a good step. For the complex cases I've worked around them for 4.5 years now and fallen back to pure SQL statements whenever needed so for me that's a non-issue nowadays.

Yes, fine with me. (Sorry for the late reply. Missed one notification-email.)
We'd insert one object inbetween, in the comparison, to make clear if "any of" or "all of this" are meant.

Should a new issue be created for this utility method, which returns an array of identifiers for a given collection of entities or value objects? Has this already been done?

Just a side note: Fixing this issue would also make life easier on the view level - for example, the ability to use the Default Fluid Pagebrowser Widget, which depends on queries to be built with QOM (as the widget currently cannot handle "statement" queries). And since Core Support for sys_categories has been added, it's even more important to provide an efficient way to handle "qualified" mm tables.