Rooduef
3 weeks ago

Get model by min() one of fields grouped by another field

Posted 3 weeks ago by Rooduef

Hi everyone.

Please help me to solve the next problem. I have two tables in the database - events and schedules.

Their schemes:

              Table "public.events"
       Column       |              Type              |                  
--------------------+--------------------------------+
 id                 | bigint                         |
 name               | character varying(255)         |
 description        | text                           |
 duration           | character varying(255)         |
 created_at         | timestamp(0) without time zone |
 updated_at         | timestamp(0) without time zone |



              Table "public.schedules"
      Column      |              Type              | 
------------------+--------------------------------+
 id               | bigint                         |
 event_id         | integer                        |
 date             | timestamp(0) without time zone |
 price            | character varying(255)         |
 created_at       | timestamp(0) without time zone |
 updated_at       | timestamp(0) without time zone |

schedules.event_id is the foreign key references on events.id

I need to fetch the nearest schedule for each event. With raw SQL I can do it with this query:

SELECT s.*
FROM schedules rs
INNER JOIN
  (SELECT event_id,
          min(date) min_date
   FROM schedules
   GROUP BY event_id
   ) s1 ON s1.event_id = s.event_id
WHERE s1.min_date = s.date

But how can I do this with Eloquent?

Thanks for help.

Please sign in or create an account to participate in this conversation.