phpmysqllaravelhas-many-through

Laravel Eloquent query 'hasManyThrough' to replace inefficient query


Ok so I have a scenario where i need to get all active jobs allocated to a particular user.

I have the following tables (just the columns of any note shown)

Job

number (integer)
is_active (boolean - default as true / 1)

JobUser

id (integer)
job_number (integer)
user_id (integer)

User

id (integer)

in my user model I have:

public function jobs()
    {
        return $this->hasManyThrough( Job::class, JobUser::class, 'user_id', 'number', 'id', 'id' );
    }

So once I have loaded the user I expect to be able to use the following: $jobs = $user->jobs()->where('is_active', 1)->get();

However I get an error as follows:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'jobs.App\Models\JobUser' in 'where clause' (SQL: select * from `jobs` where `jobs`.`App\Models\JobUser` is null and `jobs`.`App\Models\JobUser` is not null)

No idea whats wrong or if there is another way to do this. The original code in this section literally gets all rows from the JobUser wherethe user_id matches. It then uses a for loop of these to get an array of job numbers. This is then filtered $jobs = Job::select( 'jobs.*' )->where( 'is_active', '=', 1 )->whereIn( 'number', $userJobs )->get() but this is not efficient at all and creates queries like this (using laravel debug bar)

select * from `job_users` where `user_id` = 77 and `job_number` > 0 and `stage` = 'desktop'
select count(*) as aggregate from `jobs` where `is_active` = 1 and `number` in (40830, 40877, 40559, 40978, 41037, 40879, 40588, 40759, 414055, 56236, 55216, 42105, 41746, 56256, 56230, 42337, 56252, 43636, 43435, 56264, 44120, 44283, 44504, 44117, 45363, 56275, 56276, 45479, 56290, 43799, 56340, 56432, 56557, 56559, 56561, 56563, 56565, 56621, 56677, 56673, 56809, 56810, 56812, 57907, 56755, 57698, 57128, 57132, 57141, 57239, 57268, 58016, 66341, 57670, 56986, 57720, 59826, 60898, 59809, 57830, 57831, 57832, 57833, 57834, 57835, 59634, 59828, 61480, 63028, 58242, 61234, 58564, 59730, 61578, 59283, 59814, 60897, 66342, 59825, 61500, 60183, 60896, 59518, 59789, 61501, 59728, 62923, 62424, 59827, 60015, 60893, 60895, 60160, 60190, 61000, 60899, 61237, 66162, 64079, 60629, 61233, 61502, 61043, 66340, 61497, 62420, 61355, 61431, 61405, 62422, 61499, 61504, 61506, 61508, 61510, 61511, 61512, 61513, 65824, 60218, 62710, 62167, 65523, 62719, 63032, 62426, 66339, 66338, 66337, 64851, 65555, 63595, 64917, 64321, 66343, 68378, 66344, 66345, 66347, 66352, 66353, 66346, 66392, 68397, 70486, 67644, 67491, 67648, 67487, 67669, 70497, 70485, 68053, 68545, 70487, 70492, 70416, 69223, 69224, 69226, 69227, 69228, 69229, 70490, 70512, 70491, 70151, 70495, 70488, 70493, 70483, 70484, 70229, 69769, 70496, 69419, 70482, 70610, 70717, 70582, 70668, 70772, 70776, 70777, 70778, 70779, 70780, 70781, 70782, 70783, 70784, 70785, 70786, 70787, 70788, 70789, 70790, 70791, 70869, 70919, 70979, 71383, 71002, 71097, 71096, 71223, 71225, 71226, 71227, 71228, 71231, 71237, 71283, 71310, 71324, 71325, 71326, 71327, 71328, 71329, 71330, 71331, 71372, 71378, 71032, 71402, 71643, 71719, 71604, 71720, 71721, 71722, 71723, 71724, 71725, 71726, 71728, 71729, 71730, 71731, 71733, 71734, 71735, 71736, 71737, 71738, 71768, 72141, 71859, 71860, 71861, 71862, 71924, 72011, 72013, 72014, 72015, 72016, 72018, 72019, 72020, 72021, 72022, 72023, 72166, 72269, 72277, 72059)

the code then proceeds to do further queries to get the data for the rows to show in the output table. It takes .5s and uses 7Mb

thanks

** EDIT ** I have corrected the jobs() function in users to actually use hasManyThrough and I now do not get the error but i now get no rows returned from the query ( it should be 43 rowsin the actual example on my app)


Solution

  • So In the end i reread the docs and documented the Jobs function. The following worked.

        public function jobs()
        {
            return $this->hasManyThrough(
                Job::class, // TargetTable
                JobUser::class, // Intermediate table
                'user_id', // Foreign key on intermediate table for current table
                'number', // Foreign key on target table to intermediate table
                'id', // Local key on current table used by intermediate table
                'job_number' // Local key on intermediate table to target table
            );
        }