Trying to use Prisma join I have this SQL script ```SELECT c Prisma #orm-help

Trying to use Prisma.join I have this SQL script ...

Chip Clark

08/10/2021, 6:22 PM

Trying to use Prisma.join I have this SQL script

Copy code

SELECT        contcomp.ContactCompanyID, contcomp.CompanyName, contcomp.CompanyWebSite, contact.ContactCompanyID AS Expr1, contact.ContactID, contact.LastName, contact.FirstName, contact.MiddleName, 
                         contact.PreferredFirstName, contact.DisplayName, contact.InterActionID, contact.EmailAddress, contact.Prefix, contact.Suffix, contact.JobTitle
FROM            dbo.ContactCompany AS contcomp INNER JOIN
                         dbo.Contact AS contact ON contcomp.ContactCompanyID = contact.ContactCompanyID

and I'm trying to convert to a prisma.queryRaw

Copy code

let rawView = 'SELECT contcomp.ContactCompanyID, contcomp.CompanyName, contcomp.CompanyWebSite, ' +
                  'dbo.Contact.ContactCompanyID AS Expr1, dbo.Contact.ContactID, dbo.Contact.LastName, ' +
                  'dbo.Contact.FirstName, dbo.Contact.MiddleName, dbo.Contact.PreferredFirstName, ' +
                  'dbo.Contact.DisplayName, dbo.Contact.InterActionID, dbo.Contact.EmailAddress, ' +
                  'dbo.Contact.Prefix,  dbo.Contact.Suffix, dbo.Contact.JobTitle ' +
                  'FROM dbo.ContactCompany AS contcomp ' +
                  '(${Prisma.join(dbo.Contact)}) ON contcomp.ContactCompanyID = contact.ContactCompanyID ';

I get Invalid prisma.queryRaw:

Incorrect syntax near '$'.

Looking at the document here: https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#tagged-template-helpers

✅ 1

Chip Clark

08/10/2021, 8:26 PM

I have tried this:

let rawView = 'SELECT contcomp.ContactCompanyID, contcomp.CompanyName, contcomp.CompanyWebSite, ' +

'contact.ContactCompanyID AS Expr1, contact.ContactID, contact.LastName, ' +

'contact.FirstName, contact.MiddleName, contact.PreferredFirstName, ' +

'contact.DisplayName, contact.InterActionID, contact.EmailAddress, ' +

'contact.Prefix,  contact.Suffix, contact.JobTitle ' +

'FROM dbo.ContactCompany AS contcomp ' +

'INNER JOIN dbo.Contact AS contact ON contcomp.ContactCompanyID = contact.ContactCompanyID ';

But I get the following error: Message:

The multi-part identifier "contcomp.ContactCompanyID" could not be bound.

This works on SQL side.

Chip Clark

08/10/2021, 8:50 PM

This works:

let rawView = 'SELECT contcomp.ContactCompanyID [ContactCompanyID], contcomp.CompanyName [CompanyName], ' +

'contcomp.CompanyWebSite [CompanyWebSite], contact.ContactCompanyID [Expr1], ' +

'contact.ContactID [ContactID], contact.LastName [LastName], ' +

'contact.FirstName [FirstName], contact.MiddleName [MiddleName], contact.PreferredFirstName, ' +

'contact.DisplayName, contact.InterActionID, contact.EmailAddress, ' +

'contact.Prefix,  contact.Suffix, contact.JobTitle ' +

'FROM dbo.ContactCompany AS contcomp ' +

'INNER JOIN dbo.Contact AS contact ' +

'ON contcomp.ContactCompanyID = contact.ContactCompanyID ';

Ryan

08/12/2021, 6:10 AM

@Chip Clark 👋 Do you want to pass field names dynamically?

Chip Clark

08/12/2021, 4:06 PM

Yes... that would be awesome!

Ryan

08/13/2021, 7:24 AM

That isn’t easy to achieve unfortunately. Have a look at this issue from the library that we use internally for templating raw queries.

4 Views

Open in Slack

Previous Next