Laravel Mysql DB join 2 polymorphic parents

Question

I am trying to create a query that can handle joining multiple polymorphic parents.

$this->query->join('time_records AS time', function($join) use($taskTable, $projectTable) {
    $join->on('time.parent_id', '=', $taskTable . '.id', 'and')
        ->on('time.parent_type', '=', Task::class, 'and')
        ->on('time.parent_id', '=', $projectTable . '.id', 'or')
        ->on('time.parent_type', '=', Project::class, 'and');
});

This fails due to it not liking strings instead of column names. But I do feel this does not seem right anyway.

This is just one part of the overall report class. In this case there is 2 tables that the polymorphic table time_records needs to join to. but it needs to be 1 join basically. So all the time records that are joined are under the time name, so that the columns that are used all fall in line with the structure of the overall query.

I join many tables here, to create a kind of fake hierarchy.

categories.name as lev1, projects.name AS lev2, tasks.name AS lev3, time.time AS time

That is somewhat how I structure the select so a collection can then group the name and then groups the projects under it and down, ending up with a collection hierarchy that my application can read / understand.

All I am looking for is an end result where I can have a reference to the time_records table that contains data for the row based on if its the project or the task (so if its a project then lev3 would just be null/blank). I don't mind if its not a join, or if it is even just a raw SQL query. I just cannot wrap my head around the proper way to end up with the result I am looking for while keeping it to 1 query (since the collection is taxing enough).

Answer 1

I figured out a way and its more a change of mindset.

Rather then basing the query on the order you want, just adjust the select. So if you want data from a table, add select arguments for them, if not "hide" the table in the results, but always have it joined.

This part I did not show in my question and I do see now it was fairly important in isolating why this was a difficult one to figure out.

Reverse the order, rather then going from the top level down, go from the bottom up, so start with time_records as the main table for the query and join all the rest, that way both tables that relate to it can be joined simpler.

So in the end the answer was that I was totally looking at this the wrong way. it helped to write out the raw SQL output to find this out and manually query.