Questions & Answers

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"));