Related objects: Use custom SQL criteria to join?

1

I want two to join two tables via getCollectionGraph().

Trailers

id title
1 My Trailer Name 1
2 My Trailer Name 2

Dates

id title trailer_ids
3 My Date Name 3 1,2

The result should be the following for a JSON API:

[
    {
        id: 1,
        title: "My Trailer Name 1",
        dates: [
            {
                id: 3,
                title: "My Date Name 3"
            }
            …
        ]
    }
    …
]

A SQL join could be this:

FIND_IN_SET(Trailers.id, Dates.trailer_ids) > 0

Is there a way to add this criteria to getCollectionGraph() in the Function or Schema?

$modx->getCollectionGraph('Trailers', '{ "Dates":{} }', $q);

Maybe I am thinking in the wrong direction and having a separate table for trailer_ids is a better choice to set a classic relation. But im open for help and comments to get the right direction. :yum:

2

You can add a filter condition to $q (xPDOQuery) the same as when using getCollection().

3

Thats true @halftrainedharry. But for one-to-many relation its difficult?

I want to do this :thinking:

$q = $this->modx->newQuery("Trailers");
$q->join("Dates", "Dates", "FIND_IN_SET(Trailers.id, Dates.trailer_ids) > 0");
$trailers = $this->modx->getCollectionGraph("Trailers", '{ "Dates": {}}', $q);
foreach ($trailers as $trailer) {
    $trailerData = $trailer->toArray();
    $trailerData["dates"] = $trailer->getMany("Dates")->toArray();
}
4

I believe getCollectionGraph() can only be used if the relationships between the tables are properly defined in the schema. And I don’t think this is possible when one of the fields contains a list.

So I guess you either have to use a “correct” database schema with an additional junction-table, or you have to query the data without using getCollectionGraph().

1 Like