Error in query execution

@tykus okay am trying this

@tykus Now I have made the changes in the query as you have mentioned:

public function meetingSankey() {
        $returnArray = array();
        $result = DB::query()->fromSub(
            typeformContact::selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS day")
            ->where("client_signed", 1)
            ->groupByRaw('DATE(created_at)')
    
            ->union(LawyerOpportunityMeeting::selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS day")
            ->where("contact_type", "typeform")
            ->groupByRaw('DATE(created_at)'))
    
            ->union(dripfeedTarget::selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [360, 361])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [361])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [362])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [363])
            ->groupByRaw('DATE(date_added)')), 'TEMP')
            ->whereRaw('DATE(day) BETWEEN ? AND ?', [Carbon::create(2023,1,1,0)->toDateString(), Carbon::now()->toDateString()])->get();

        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);
    }

Still the error while hitting the application is:

Illuminate\Database\QueryException: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "client_signed" does not exist
LINE 1: ...created_at) AS day from "typeform_contacts" where "client_si...

And when I separately execute the query built:

(select * from ((select 'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS day from "typeform_contacts" where "client_signed" = 1 group by DATE(created_at)) union (select 'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS day from "lawyer_opportunity_meetings" where "contact_type" = typeform group by DATE(created_at)) union (select '4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (360, 361) group by DATE(date_added)) union (select '4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (361) group by DATE(date_added)) union (select '4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (362) group by DATE(date_added)) union (select '4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (363) group by DATE(date_added))) as "TEMP" where DATE(day) BETWEEN 2023-01-01 AND 2023-02-01)

This time it shows the following error:

ERROR:  column "typeform" does not exist
LINE 1: ...wyer_opportunity_meetings" where "contact_type" = typeform g...

Kindly reply if possible.

@ankur_g regarding this:

Undefined column: 7 ERROR:  column "client_signed" does not exist

Is your application connecting to the correct database; there is really no reason for the column to be non-existent.

And in the case of this error

column "typeform" does not exist

Can you modify the query to include quotation marks around the literal 'typeform'

where "contact_type" = 'typeform'

@tykus yes same regarding

Undefined column: 7 ERROR:  column "client_signed" does not exist

I am mentioning the model name over there so the column names must be present and the column exist and regarding

where "contact_type" = 'typeform'

I have tried

->whereRaw("contact_type = typeform")

and

->where("contact_type",'typeform')

or

->where("contact_type","typeform")

But still it shows the same error

ERROR:  column "typeform" does not exist
LINE 1: ...wyer_opportunity_meetings" where "contact_type" = typeform g...

@ankur_g how do you deduce this...

I am mentioning the model name over there so the column names must be present and the column exist

Can you execute a DESCRIBE query before this?

public function meetingSankey() {
    dd(\DB::select('DESCRIBE typeform_contacts'));
    // ...

Let's deal with the client_signed issue, because for typeform issue, I believe the generated query is simply missing quotation marks, and will not need anything in the Builder except ->where("contact_type",'typeform')

@tykus Sir I have made the changes now as follows:

$result = DB::query()->fromSub(
            
            typeformContact::selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS day")
            ->where("client_signed", 1)
            ->groupByRaw('DATE(created_at)')
    
            ->union(LawyerOpportunityMeeting::selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS day")
            ->whereRaw('"contact_type" = \'typeform\'')
            ->groupByRaw('DATE(created_at)'))
    
            ->union(dripfeedTarget::selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [360, 361])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [361])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [362])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [363])
            ->groupByRaw('DATE(date_added)')), 'TEMP')
            ->whereRaw('DATE(day) BETWEEN \'?\' AND \'?\'', [Carbon::create(2023,1,1,0)->toDateString(), Carbon::now()->toDateString()])->get();

        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);
    }

The error now states:

Illuminate\Database\QueryException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Although the query which is getting built:

(select * from ((select 'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS day from "typeform_contacts" where "client_signed" = 1 group by DATE(created_at)) union (select 'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS day from "lawyer_opportunity_meetings" where "contact_type" = 'typeform' group by DATE(created_at)) union (select '4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (360, 361) group by DATE(date_added)) union (select '4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (361) group by DATE(date_added)) union (select '4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (362) group by DATE(date_added)) union (select '4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day from "dripfeed_targets" where "feed_id" in (363) group by DATE(date_added))) as "TEMP" where DATE(day) BETWEEN '2023-01-01' AND '2023-02-01')

It returns the correct result

@ankur_g you're escaping the quotation marks unnecessarily in my opinion; I believe you are being misled by looking at the toSql result! Whenever

This should work:

$result = DB::query()->fromSub(
            
            typeformContact::selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS day")
            ->where("client_signed", 1)
            ->groupByRaw('DATE(created_at)')
    
            ->union(LawyerOpportunityMeeting::selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS day")
            ->where('contact_type', 'typeform')
            ->groupByRaw('DATE(created_at)'))
    
            ->union(dripfeedTarget::selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [360, 361])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [361])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [362])
            ->groupByRaw('DATE(date_added)'))
    
            ->union(dripfeedTarget::selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS day")
            ->whereIn('feed_id', [363])
            ->groupByRaw('DATE(date_added)')), 'TEMP')
            ->whereRaw('DATE(day) BETWEEN ? AND ?'', [Carbon::create(2023,1,1,0)->toDateString(), Carbon::now()->toDateString()])->get();

        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);
    }

Did you really puke all of that nonsense onto the thread??? Where did I say

dd(DB::query()->select('DESCRIBE typeform_contacts'));

I asked for the result of this expression; you understand the difference, right?

dd(DB::select('DESCRIBE typeform_contacts'));

@tykus sorry I was not clear about it, I am removing that reply