CODEWITHSUNDEEP
mysqllaraveleloquentforeign-keysleft-join
Line in Linus
Line in Linus

Reputation: 420

Limit/order result with 3 tables

So I'm trying to limit the result of the subquery to one per row

    Tables:    
    Companies: id
    Events: id, title, date
    Relations: local_id, foreign_id

select `companies`.*, `e`.`title` 
from `companies` 
left join (
  select `events`.*, `relations`.`local_id` as `pivot_local_id`, `relations`.`foreign_id` as `pivot_foreign_id` 
    from `events` inner join `relations` on `events`.`id` = `relations`.`foreign_id` 
    where `relations`.`local_id` = companies.id 
    order by `date` desc limit 1 
) e on e.pivot_local_id = companies.id

This Q returns "#1054 - Unknown column 'companies.id' in 'where clause".

In the end I want to fetch company.* and 'the latest event title' (if there are any) for each company.

To further complicate things I would love to know if this can be accomplished with Laravel 5.3's Eloquent ORM.

Upvotes: 1

Views: 46

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

The table companies is not visible/available is subquery so you have the error the fact that is present in main query don't is enough.

Then you must join the table companies inside the subquery eg (I don't know if the subquery is right for your scope)

select `companies`.*, `e`.`title` 
from `companies` 
left join (
  select `events`.*, `relations`.`local_id` as `pivot_local_id`, `relations`.`foreign_id` as `pivot_foreign_id` 
    from `events` inner join `relations` on `events`.`id` = `relations`.`foreign_id` 
    inner join companies on `relations`.`local_id` = companies.id 
    order by `date` desc limit 1 
) e on e.pivot_local_id = companies.id

Upvotes: 1

Eugene
Eugene

Reputation: 1639

So, this is your (incorrect) query cleaned up:

SELECT c.*, e.title
FROM companies c
LEFT JOIN (
SELECT ev.*, r.local_id AS pivot_local_id, r.foreign_id AS pivot_foreign_id
FROM events ev
INNER JOIN relations r on ev.id = r.foreign_id
WHERE r.local_id = c.id 
ORDER BY `date` DESC LIMIT 1 
) e ON e.pivot_local_id = c.id;

And this (hopefully) should be what you're intending to implement:

SELECT c.*, ev.title
FROM companies c
LEFT JOIN relations r ON c.id = r.local_id
LEFT JOIN events ev on ev.id = r.foreign_id
WHERE ev.title IS NOT NULL
ORDER BY `date` DESC LIMIT 1;

Notes: you probably don't need the WHERE ev.title IS NOT NULL, and you should associate date to a table alias.

Good luck!

Upvotes: 0

Related Questions