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.