menu

Questions & Answers

How can I write this query (subquery and left join) in Laravel?

I have such a SQL query in PostgreSql. I want to write this query in a Laravel controller. How can I write this query in Laravel?

SELECT
  hour,
  EXISTS(
    SELECT 1 FROM meetings AS m
    WHERE m.date = '2022-01-15'
      AND m.meeting_period_id = mp.id
      AND (m.student_id = 2
        OR EXISTS(
            SELECT * from users AS u
            LEFT JOIN meetings AS m2
                ON u.id = m2.teacher_id
                  AND m2.date = '2022-01-15'
                  AND m2.meeting_period_id = 1
            WHERE m2.id IS NULL AND u.user_type = 2
        ) = FALSE
      )
  ) = FALSE AS is_available
FROM meeting_periods AS mp

I don't know how to do subquery and left join in Laravel.

I used DB::raw as a last resort. I can't receive requests. For example (m.date = '2022-01-15'). I need to get the date value with a request. I don't know anything about left join, subquery and db:raw.

$availability = DB::select(DB::raw(
  "SELECT
      hour,
      EXISTS(
        SELECT 1 FROM meetings AS m
        WHERE m.date = '2022-01-15'
          AND m.meeting_period_id = mp.id
          AND (m.student_id = 4
            OR EXISTS(
                SELECT * from users AS u
                LEFT JOIN meetings AS m2
                    ON u.id = m2.teacher_id
                      AND m2.date = '2022-01-15'
                      AND m2.meeting_period_id = 1
                WHERE m2.id IS NULL AND u.user_type = 2
            ) = FALSE
          )
      ) = FALSE AS is_available
  FROM meeting_periods AS mp"));
Comments:
2023-01-17 00:48:03
If you've been struggling for hours for hours, there must be some code to share. Now we even don't know if you are using Eloquent or the DB facade. There are multiple ways of handeling this.
2023-01-17 00:48:03
Normally I have used "Eloquent" in all my code up to this point. But as I said, I couldn't set it up for this query.
2023-01-17 00:48:03
Please clarify via edits, not comments. See How do comment replies work? to learn to use @x to notify 1 non-sole non-poster commenter x per comment about that comment. Posters, sole commenters & followers of posts always get notified. Without @ other commenters get no notification.
2023-01-17 00:48:03
A minimal reproducible example includes cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK.
Answers(0) :