CODEWITHSUNDEEP
phplaravelpostgresqleloquent
Aleksej_Shherbak
Aleksej_Shherbak

Reputation: 3068

Undefined column: 7 ERROR: column does not exist

I'm using PostgreSQL. This error is happening when I use "" quotes instead of ''. It's simple, I will show you. This SQL query gives me described in the question title error 

select "sub"."code", "sub"."customer_id", "sub"."data", "sub"."event_count", "sub"."created_at"
from (select "customer_event_types"."code",
             "customer_events"."customer_id",
             "customer_events"."data",
             count(customer_events.id)       event_count,
             max(customer_events.created_at) created_at
      from "customers"
               inner join "customer_events" on "customer_events"."customer_id" = "customers"."id"
               inner join "customer_event_types"
                          on "customer_events"."customer_event_type_id" = "customer_event_types"."id"
      group by "customer_event_types"."code", "customer_events"."customer_id", "customer_events"."data"
      having count(customer_events.id) = 1) as "sub"
where "sub"."code" = "registration"

Do you see "registration" in the last line? It gives me the error.

[42703] ERROR: column "registration" does not exist

Here is example of right code: 

select "sub"."code", "sub"."customer_id", "sub"."data", "sub"."event_count", "sub"."created_at"
from (select "customer_event_types"."code",
             "customer_events"."customer_id",
             "customer_events"."data",
             count(customer_events.id)       event_count,
             max(customer_events.created_at) created_at
      from "customers"
               inner join "customer_events" on "customer_events"."customer_id" = "customers"."id"
               inner join "customer_event_types"
                          on "customer_events"."customer_event_type_id" = "customer_event_types"."id"
      group by "customer_event_types"."code", "customer_events"."customer_id", "customer_events"."data"
      having count(customer_events.id) = 1) as "sub"
where "sub"."code" = 'registration'

It works for me. So, the question. How to explain for Eloquent ORM that I need 'registration' instead of "registration" ?

Here is my Eloquent code: 

$set = DB::query()->fromSub(function ($query) {
    $query->from('customers')->select('customer_event_types.code',
        'customer_events.customer_id',
        'customer_events.data',
        DB::raw('count(customer_events.id) event_count'),
        DB::raw('max(customer_events.created_at) created_at'))
        ->join('customer_events', 'customer_events.customer_id', '=', 'customers.id')
        ->join('customer_event_types', 'customer_events.customer_event_type_id', 'customer_event_types.id')
        ->groupBy('customer_event_types.code', 'customer_events.customer_id', 'customer_events.data')
        ->having(DB::raw('count(customer_events.id)'), '=', 1);
}, 'sub')
    ->select('sub.code', 'sub.customer_id', 'sub.data', 'sub.event_count', 'sub.created_at')
    ->when($firstItem, function ($query, $events) {
        // making conditions where dynamic 

        $whereColumn = [];
        foreach ($events as $event) {

            $whereColumn[] = ["sub.code", "=", (string)$event->event_code];

            if (isset($event->after_than_minutes_ago) && !empty($event->after_than_minutes_ago) && is_numeric($event->after_than_minutes_ago)) {
                $whereColumn[] = ["sub.created_at", '>', Carbon::now()->subMinutes($event->after_than_minutes_ago)->toString()];
            }
            // произошло раньше, чем <число> минут назад
            if (isset($event->before_than_minutes_ago) && !empty($event->before_than_minutes_ago) && is_numeric($event->before_than_minutes_ago)) {
                $whereColumn[] = ["sub.created_at", '<', Carbon::now()->subMinutes($event->before_than_minutes_ago)->toString()];
            }

            $query->whereColumn($whereColumn);
            $whereColumn = [];
        }
        return $query;
    })
    ->get();

As you can see this code more difficult than SQL equivalent. It's making where conditions from input data. But It seems not important here. I have the same error, that related on quotes.

SQLSTATE[42703]: Undefined column: 7 ERROR: column "registration" does not exist LINE 1: ...r_events.id) = $1) as "sub" where ("sub"."code" = "registrat... ^ (SQL: select "sub"."code", "sub"."customer_id", "sub"."data", "sub"."event_count", "sub"."created_at" from (select "customer_event_types"."code", "customer_events"."customer_id", "customer_events"."data", count(customer_events.id) event_count, max(customer_events.created_at) created_at from "customers" inner join "customer_events" on "customer_events"."customer_id" = "customers"."id" inner join "customer_event_types" on "customer_events"."customer_event_type_id" = "customer_event_types"."id" group by "customer_event_types"."code", "customer_events"."customer_id", "customer_events"."data" having count(customer_events.id) = 1) as "sub" where ("sub"."code" = "registration" and "sub"."created_at" > "Fri Aug 30 2019 10:03:14 GMT+0000" and "sub"."created_at" < "Fri Aug 30 2019 10:38:14 GMT+0000"))

Upvotes: 0

Views: 14853

Answers (1)

Mark
Mark

Reputation: 5239

From the docs:

The whereColumn method may be used to verify that two columns are equal:

You should probably use whereIn or where which verifies the value of a column is equal to something, instead of whereColumn which verifies if two columns are equal. (Then the query should be generated correctly with single quotes).

Upvotes: 1

Related Questions