SQL exists in Laravel 5 query builder

Question

Good morning,

I've been trying for quite a lot of time to translate this query(which returns an array of stdClass) into query builder so I could get objects back as Eloquent models.

This is how the query looks like untranslated:

$anketa = DB::select( DB::raw("SELECT * 
                                         FROM v_anketa a 
                                        WHERE not exists (select 1 from user_poeni where anketa_id=a.id and user_id = :lv_id_user)
                                        Order by redni_broj limit 1"
                                     ), array(   'lv_id_user' => $id_user,
                ));

I have tried this, but it gives a syntax error near the inner from in the subquery:

$anketa = V_anketa::selectRaw("WHERE not exists (select 1 from user_poeni where anketa_id=a.id and user_id = :lv_id_user)", array('lv_id_user' => $id_user,)
                            )->orderBy('redni_broj')->take(1)->first();

The problem is this exists and a subquery in it. I couldn't find anything regarding this special case.

Assume each table has an appropriate Eloquent model. V_anketa is a view. The db is postgresql.

Damien Pirsy · Accepted Answer · 2015-11-12 08:25:18Z

4

As far as the query goes I believe this should work:

$anketa = V_anketa::whereNotExists(function ($query) use ($id_user) {
                $query->select(DB::raw(1))
                      ->from('user_poeni')
                      ->where('anketa.id', '=', 'a.id')
                      ->where('user_id', '=', $id_user);
            })
           ->orderBy('redni_broj')
           ->first();

but I'm not clear on what do you mean by "assuming every table has an Eloquent model" and "V_anketa" is a view...

edited Nov 12, 2015 at 8:25

answered Nov 12, 2015 at 8:23

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

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

2 Comments

EldarGranulo Over a year ago

This is exactly what I needed. I have just did it myself, I will accept your answer.

EldarGranulo Over a year ago

Thats just added information. For example the table user_poeni has an Eloquent model name UserPoeni etc. I added the information about V_anketa since it itself points to a view not to a table. I'm still quite new to Laravel so I assumed there could be some difference there.

TheNiceGuy · Accepted Answer · 2015-11-12 08:14:26Z

1

Assuming the SQL query is correct, this should work:

$anketa = DB::select(sprintf('SELECT * FROM v_anketa a WHERE NOT EXISTS (SELECT 1 FROM user_poeni WHERE anketa_id = a.id AND user_id = %s) ORDER  BY redni_broj LIMIT 1', $id_user));

If you want to get back an Builder instance you need to specify the table:

$anketa = DB::table('')->select('');

If you however, want to get an Eloquent Model instance, for example to use relations, you need to use Eloquent.

edited Nov 12, 2015 at 8:14

answered Nov 12, 2015 at 8:05

TheNiceGuy

3,7609 gold badges39 silver badges69 bronze badges

3 Comments

EldarGranulo Over a year ago

This still returns an array of stdClass objects, not eloquent models.

TheNiceGuy Over a year ago

What do you mean? This won't return "eloquent models" (I assume you mean eloquent instances) as you'r enot using Eloquent, but the query builder?

EldarGranulo Over a year ago

I probably haven't phrased the question right, my mistake. I want something like my try, i.e V_anketa::something which will give me results of type V_anketa and not an array of stdClass.

Collectives™ on Stack Overflow

SQL exists in Laravel 5 query builder

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related