laravel raw join query

Question

I have a function that accepts raw where conditions and joins:

query('data',['fieldA','fieldB'], 'fieldA > 10 AND fieldB < 20', 'LEFT JOIN users ON data.user_id = users.id');

function query($table, $keys = [], $where = '', $joins = '') {
   $query = DB::table($table)->select($keys);
   if(!empty($where)) {
      $query=$query->whereRaw($where);
   }
   if(!empty($joins)) {
      $query=$query->?????????????
   }
   return $query->get();
}

How do I use the raw join with the query builder the way I can use whereRaw for the where condition?

Why are you essentially deconstructing Laravel's query builder into an insecure function? — Devon Bessemer
– Devon Bessemer, Commented Oct 4, 2017 at 14:07
create more generic kind of method for your joins and pass parameters accordingly. Utilize the feature of ORM not raw statement if possible. — BetaDev
– BetaDev, Commented Oct 4, 2017 at 14:09
@Devon I build migration helpers for a large DB migration from an old system. The migrations will be the only consumers of the function. I therefore have no security concerns — Chris
– Chris, Commented Oct 4, 2017 at 14:09
did you try $query=$query->leftJoin('users', 'data.user_id', '=', 'users.id') and pass you join parameter separatly and construct this query. — BetaDev
– BetaDev, Commented Oct 4, 2017 at 14:12

David Meth · Accepted Answer · 2020-12-02 16:21:06Z

6

Haven't good smell but works

    \DB::table('data')->join('user', function($join){
            $join->on(\DB::raw('( `data`.`user_id` = `user.id` or (`data`.`user_id` is null and `data`.`other_field` is null)) and 1 '),'=',\DB::raw('1'));      
        })

answered Dec 2, 2020 at 16:21

David Meth

611 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Devon Bessemer · Accepted Answer · 2017-10-04 14:30:36Z

3

Don't bother with the query builder if you're just using raw expressions on everything.

Option 1: Utilize PDO

PDO is the underlying driver used by Laravel. To get the PDO object, run:

$pdo = DB::connection()->getPdo();

Option 2: Run raw queries

You can run entire selects through Laravel without "building" a query:

DB::select("SELECT * FROM table WHERE ..");

This even allows parameter binding when you need it.

https://laravel.com/docs/5.5/database

edited Oct 4, 2017 at 14:30

answered Oct 4, 2017 at 14:28

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

Comments

BetaDev · Accepted Answer · 2017-10-04 14:30:43Z

Here I have put some idea how can you make your method little bit generic. Do same thing for where clause and other part of the query.

query('data',['fieldA','fieldB'], 'fieldA > 10 AND fieldB < 20', 'LEFT', 'users', 'data.user_id','users.id');

function query($table, $keys = [], $where = '', $join_type = 'LEFT', $join_table='', $join_field1='', $join_field2='') {
   $query = DB::table($table)->select($keys);
   if(!empty($where)) {
      $query=$query->whereRaw($where);
   }
   if(!empty($joins)&&!empty($join_type)&&$join_type=='LEFT') {
      $query=$query->leftJoin($join_table, $join_field1, '=', $join_field2)
   }
   if(!empty($joins)&&!empty($join_type)&&$join_type=='INNER') {
      $query=$query->join($join_table, $join_field1, '=', $join_field2)
   }
   ///and so on
   return $query->get();
}
//But you can make this method more generic, I just put some idea so that you can make this method more flexible
//I did not take a look at your other part of the method though.

Note: I did not put multiple joins only two table joins but you can make your method more generic form and more flexible. I just tried to put some idea that comes into my mind. If I am wrong then correct me please.
Reference: Laravel - Joins

uiuifree · Accepted Answer · 2017-10-04 14:33:26Z

-3

DB::table('data')->join('users','data.user_id','users.id')->where(~~~)

answered Oct 4, 2017 at 14:33

uiuifree

351 bronze badge

Collectives™ on Stack Overflow

laravel raw join query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related