Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Ixalmida's avatar

Query builder generates valid query but throws error in code

I have an SQL query that queries for all unverified company owners to send out a reminder email on unsubmitted applications.

The following is my query builder code:

        $unverified = DB::table('OnlineApp.applications AS app')
            ->select('own.name', 'own.email', 'usr.id', DB::raw('MAX(tok.id) as tokenId'), 'tok.token')
            ->join('OnlineApp.application_companies AS cp', 'app.id', '=', 'cp.application_id')
            ->join('OnlineApp.company_owners AS co', 'cp.company_id', '=', 'co.company_id')
            ->join('OnlineApp.owners AS own', 'co.owner_id', '=', 'own.id')
            ->join('OnlineApp.users AS usr', 'own.user_id', '=', 'usr.id')
            ->join('OnlineApp.user_tokens AS tok', 'usr.id', '=', 'tok.user_id')
            ->where('app.t_and_c2', '>', $dtStart)
            ->where('app.t_and_c2', '<', $dtEnd)
            ->where('app.status_id', 1)
            ->where('app.stage_id', 1)
            ->where('app.applicant_id', '<>', 'own.id')
            ->whereNull('own.confirmed')
            ->groupBy('own.name', 'own.email', 'usr.id', 'tok.token')
            ->get();

Which generates the following valid and working SQL query (verified in SSMS):

SELECT
    [own].[name],
    [own].[email],
    [usr].[id],
    MAX(tok.id) AS tokenId,
    [tok].[token]
FROM [OnlineApp].[applications] AS [app]
    INNER JOIN [OnlineApp].[application_companies] AS [cp] ON [app].[id] = [cp].[application_id]
    INNER JOIN [OnlineApp].[company_owners] AS [co] ON [cp].[company_id] = [co].[company_id]
    INNER JOIN [OnlineApp].[owners] AS [own] ON [co].[owner_id] = [own].[id]
    INNER JOIN [OnlineApp].[users] AS [usr] ON [own].[user_id] = [usr].[id]
    INNER JOIN [OnlineApp].[user_tokens] AS [tok] ON [usr].[id] = [tok].[user_id]
WHERE
    [app].[t_and_c2] > '2017-12-01 16:48:31.000' AND
    [app].[t_and_c2] < '2017-12-20 16:48:31.000' AND
    [app].[status_id] = 1 AND
    [app].[stage_id] = 1 AND
    [app].[applicant_id] <> own.id AND
    [own].[confirmed] is null
GROUP BY
    [own].[name],
    [own].[email],
    [usr].[id],
    [tok].[token]

HOWEVER...this query does not work within code due to a data type conversion that should not happen.

Here is the error:

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[22018]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'own.id' to data type int.

All "id" fields are auto-numbered [PK, int, not null], so they are never, ever going to be of type [nvarchar]. Also, this is not my first time having this issue. I also had to remove all foreign keys from my migrations because it would cause the same error within Eloquent.

Any ideas on how to fix this problem?

Note: I am using Laravel 5.3 on Windows 10 and connecting to an Azure DB instance.

0 likes
1 reply
bobbybouwmann's avatar

Well the mistake is in your select query. In this case it's looking for the own.id but it's not in the select anymore. If you add it to the select you should be good to go ;)

->select('own.id', 'own.name', 'own.email', 'usr.id', DB::raw('MAX(tok.id) as tokenId'), 'tok.token')
1 like

Please or to participate in this conversation.