How to run sql query stored in a variable with Laravel Eloquent?

Question

I want to run a sql query that I store in a variable in Laravel. It's really easy to do in CodeIgniter with just running $this->db->sql($sql). I wonder if there is a way like this in Laravel.

  $sql = "SELECT u.*,c.city_name,prov.province_name FROM users u ".
        "LEFT JOIN cities c ON c.city_id=u.city_id".
        "LEFT JOIN provinces prov ON prov.province_id=u.province_id".
        "WHERE u.id= ?";

I tried to execute it using DB::table($sql) but I guess that's not the way.

EDIT: Using the following is working fine but I still wonder if I just can run something like I do in CI with running $this->db->query($sql).

$user = User::select('users.*','cities.city_name','provinces.province_name')
           ->where('users.id', Auth::id())
           ->leftJoin('cities','cities.city_id','=','users.city_id')
           ->leftJoin('provinces','provinces.province_id','=','users.province_id')
           ->get()->first();

As @Martin Adiputra said you need to use DB::select and pass parameters as shown in answer — Leena Patel
– Leena Patel, Commented Sep 12, 2018 at 4:57

Martin Adiputra · Accepted Answer · 2018-09-12 04:15:27Z

2

you can use this

$users = DB::select('select * from users where active = ?', [1]);

refer the docs https://laravel.com/docs/5.7/database#running-queries

so if you

$sql = "SELECT u.*,c.city_name,prov.province_name FROM users u ".
        "LEFT JOIN cities c ON c.city_id=u.city_id".
        "LEFT JOIN provinces prov ON prov.province_id=u.province_id".
        "WHERE u.id= ?";

just try

$query_result = DB::select($sql, [your parameter variable]);

edited Sep 12, 2018 at 4:15

answered Sep 12, 2018 at 3:35

Martin Adiputra

1896 bronze badges

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

1 Comment

Abaij Over a year ago

yes, i have no problem with this. but I'm looking to possibility of other way which I think is easier for me

Gio Rodriguez · Accepted Answer · 2018-09-12 03:35:41Z

0

Create eloquent relationships in models. When there is a relationship between models, you do not have to create a long sql query. You can refer to their documentation. It is really easy to understand. https://laravel.com/docs/5.6/eloquent-relationships

answered Sep 12, 2018 at 3:35

Gio Rodriguez

1461 silver badge8 bronze badges

1 Comment

Abaij Over a year ago

definitely will learn about this but I have no time at the moment so I'm still looking to other solution closer to what I expect

Sociopath · Accepted Answer · 2018-09-12 05:44:31Z

0

Try this:

$rows = DB::select(
    DB::raw($sql, array($stringids))
);

edited Sep 12, 2018 at 5:44

Sociopath

13.4k22 gold badges53 silver badges82 bronze badges

answered Sep 12, 2018 at 4:50

PHP Geek

4,0632 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to run sql query stored in a variable with Laravel Eloquent?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related