Raw Sql-select

Published 4 months ago by mlutz

Hello,

i have a problem with a raw SQL-select. This Code works in Laravel 5.2:

$massage = DB::table('course')
                     ->select(DB::raw("*,
                            (SELECT COUNT(*)
                             FROM appointments
                             WHERE p ='x'
                             AND appointments.id = course.id
                             GROUP BY A_id
                             ) as fp,
                    ...
            ...
                            "))->get();

I've got a value for the the variable "fp".

With Laravel 5.5 the value "fp" is empty.

With the raw-select the get and set attributes in the model doesn't work, too.

For a better understanding: I have a table with sport courses. The table course has a many relation to the table appointments. I need the count of the booked appointments.

How can i solve my problem?

Best regards Markus

Best Answer (As Selected By mlutz)
bobbybouwmann

I believe you need to replace ->select(DB::raw( with ->selectRaw and that should work.

Not sure, but I think there is a difference between the two now. I had this the other day as well and selectRaw seems to work!

Let me know if that helps you!

bobbybouwmann

I believe you need to replace ->select(DB::raw( with ->selectRaw and that should work.

Not sure, but I think there is a difference between the two now. I had this the other day as well and selectRaw seems to work!

Let me know if that helps you!

mlutz

Thanks for your reply. I tested the "selectRaw". The query received a value. So the first step works. :)

But the next problem is the date format. The get attribute in the model seems to be ignored. The date format is wrong. Am i right, the the attribute works only with eloquent?

Snapey
Snapey
4 months ago (897,275 XP)

Yes, this will return a collection not an Eloquent Model.

bobbybouwmann

@mlutz Can you show your current query?

mlutz

@bobbybouwmann

Here is the working select:

 $massage = DB::table('courses')
        ->selectRaw("*,
                    (SELECT COUNT(*)
                    FROM appointments
                    WHERE p ='x'
                    AND appointments.id = course.id
                    GROUP BY course_id
                    ) as fp")->get();
bobbybouwmann

So what is the remaining question?

mlutz

It's allright. @Snapey answer the question about the eloquent model.

I use Carbon for the date format and now i'm happy.

Thank you for your help

best regards markus

bobbybouwmann

Great!

Please sign in or create an account to participate in this conversation.