A JOIN With Additional Conditions Using Query Builder or Eloquent

Question

I m trying to add a condition using a JOIN query with Laravel Query Builder    lt  php   results   DB  select          SELECT DISTINCT                        FROM               rooms                  LEFT JOIN bookings                      ON rooms id   bookings room type id                   AND    bookings arrival between   and                         OR bookings departure between   and               WHERE                 bookings room type id IS NULL           LIMIT 20       array  2012-05-01    2012-05-10    2012-05-01    2012-05-10        I know I can use Raw Expressions but then there will be SQL injection points  I ve tried the following with Query Builder but the generated query  and obviously  query results  aren t what I intended    results   DB  table  rooms       - gt distinct       - gt leftJoin  bookings   function   join             join- gt on  rooms id         bookings room type id               - gt whereBetween  arrival   array  2012-05-01    2012-05-10        - gt whereBetween  departure   array  2012-05-01    2012-05-10        - gt where  bookings room type id        null      - gt get      This is the generated query by Laravel   select distinct   from  room type info      left join  bookings   on  room type info   id     bookings   room type id   where  arrival  between   and        and  departure  between   and        and  bookings   room type id  is null   As you can see  the query output doesn t have the structure  especially under JOIN scope   Is it possible to add additional conditions under the JOIN   How can I build the same query using Laravel s Query Builder  if possible  Is it better to use Eloquent  or should stay with DB  select

User · Answer

There s a difference between the raw queries and standard selects  between the DB  raw and DB  select methods    You can do what you want using a DB  select and simply dropping in the   placeholder much like you do with prepared statements  it s actually what it s doing    A small example    results   DB  select  SELECT   FROM user WHERE username       jason       The second parameter is an array of values that will be used to replace the placeholders in the query from left to right

User · Answer

If you have some params  you can do this        results   DB  table  rooms       - gt distinct       - gt leftJoin  bookings   function  join  use   param1   param2                 join- gt on  rooms id         bookings room type id             join- gt on  arrival      DB  raw      param1                 join- gt on  arrival      DB  raw      param2                    - gt where  bookings room type id        NULL      - gt get      and then return your query  return  results

User · Answer

The sql query sample like this  LEFT JOIN bookings       ON rooms id   bookings room type id     AND  bookings arrival             OR bookings departure        Laravel join with multiple conditions   - gt leftJoin  bookings   function  join  use   param1   param2         join- gt on  rooms id         bookings room type id         join- gt on function  query  use   param1   param2             query- gt on  bookings arrival         param1            query- gt orOn  departure        param2

User · Answer

I am using laravel5 2 and we can add joins with different options  you can modify as per your requirement   Option 1          DB  table  users               - gt join  contacts   function   join                     join- gt on  users id         contacts user id  - gt orOn        you add more joins here                  and you add more joins here         - gt get     Option 2       users   DB  table  users           - gt join  contacts    users id         contacts user id           - gt join  orders    users id         orders user id     you may add more joins         - gt select  users      contacts phone    orders price           - gt get     option 3       users   DB  table  users           - gt leftJoin  posts    users id         posts user id           - gt leftJoin                               you may add more joins         - gt get

User · Answer

results   DB  table  rooms                        - gt distinct                        - gt leftJoin  bookings   function  join                                                           join- gt on  rooms id         bookings room type id                                  join- gt on  arrival    gt    DB  raw   2012-05-01                                    join- gt on  arrival    lt    DB  raw   2012-05-10                                    join- gt on  departure    gt    DB  raw   2012-05-01                                    join- gt on  departure    lt    DB  raw   2012-05-10                                                       - gt where  bookings room type id        NULL                       - gt get      Not quite sure if the between clause can be added to the join in laravel   Notes    DB  raw   instructs Laravel not to put back quotes  By passing a closure to join methods you can add more join conditions to it  on   will add AND condition and orOn   will add OR condition

User · Answer

You can replicate those brackets in the left join   LEFT JOIN bookings                  ON rooms id   bookings room type id               AND    bookings arrival between   and                     OR bookings departure between   and       is  - gt leftJoin  bookings   function  join        join- gt on  rooms id         bookings room type id         join- gt on DB  raw     bookings arrival between   and   OR bookings departure between   and        DB  raw      DB  raw            You ll then have to set the bindings later using  setBindings  as described in this SO post  How to bind parameters to a raw DB query in Laravel that  39 s used on a model   It s not pretty but it works

[laravel] A JOIN With Additional Conditions Using Query Builder or Eloquent

Examples related to laravel

Examples related to laravel-4

Examples related to laravel-query-builder