Complex MYSQL statement with subquery - Derived tables

I have a complex mysql statement that I'm trying to write in Eloquent but I'm stuck with the sub/derived tables. The statement works... Can anyone help with the converting to eloquent.. or point me in the write direction.

I'm beginning to think that I should write some mysql statements the delete and recreate tables and then add the tables to the queries... What makes the most sense here?????

It's basically three tables...

tblpractitioners - Name and information about practitioner tblservicedutyscheduledprac - Hold schedule information for practitioner tblpracsuitablepositions - Suitable positions for practitioners.

This query basically pulls up a list of practitoners that are in sevice and indicates who has served last month with an x and removes anyone how has already been placed in service for that month. It allows you to pick someone to place in service. I just used set the values for the previous month query and current month query to test this out.

Select
    tblpractitioners.id,
    Concat_Ws(', ', tblpractitioners.LastName, tblpractitioners.Salutation) As PracName,
    tblpractitioners.Availability,
    tblpractitioners.LastServiceDate,
    tblpractitioners.ServiceNotes,
    tblpractitionerphones.PhoneNumber,
    tblphonetypes.Description,
    qryPreviousMonthService.Indicator,
    tblpracsuitablepositions.PracDutiesID,
    tblkuppracduties.DutyAbrv,
    tblkuppracduties.DutyDesc
From
    tblpractitioners Inner Join
    tblpractitionerphones On tblpractitionerphones.pracid = tblpractitioners.id
            And tblpractitionerphones.PhoneTypeID = tblpractitioners.PrimaryPhoneID Left Join
            tblphonetypes On tblphonetypes.id = tblpractitionerphones.PhoneTypeID Left Join
    (Select
         tblpractitioners.id,
         tblpractitioners.FirstName,
         tblpractitioners.LastName,
         tblservicedutyscheduledprac.pracid,
         tblservicedutyscheduledprac.PRACName,
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%c") As PMonth,
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%Y") As PYear,
         If(tblservicedutyscheduledprac.id Is Null, '', 'X') As Indicator
     From
         tblpractitioners Inner Join
         tblservicedutyscheduledprac On tblservicedutyscheduledprac.pracid = tblpractitioners.id
     Where
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%c") = 4 And
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%Y") = 2018) qryPreviousMonthService On
            qryPreviousMonthService.id = tblpractitioners.id Left Join
    (Select
         tblpractitioners.id,
         tblpractitioners.FirstName,
         tblpractitioners.LastName,
         tblservicedutyscheduledprac.ServiceDate,
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%c") As Month,
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%Y") As Year,
         tblservicedutyscheduledprac.pracid,
         tblservicedutyscheduledprac.PRACName
     From
         tblservicedutyscheduledprac Inner Join
         tblpractitioners On tblservicedutyscheduledprac.pracid = prac.tblpractitioners.id
     Where
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%c") = 5 And
         Date_Format(tblservicedutyscheduledprac.ServiceDate, "%Y") = 2018 And
         tblservicedutyscheduledprac.pracid <> 0) qrySuitablePostitionsExcludeSelected On
            qrySuitablePostitionsExcludeSelected.id = tblpractitioners.id Inner Join
    tblpracsuitablepositions On tblpracsuitablepositions.pracid = tblpractitioners.id Inner Join
    tblkuppracduties On tblkuppracduties.id = tblpracsuitablepositions.PracDutiesID
Where
    tblpractitioners.InService = 1 And
    tblpracsuitablepositions.PracDutiesID = 1

jlrdw

6 years ago

Best Answer

Level 75

If that works use as is, eloquent (active record) is not for everything. Complex queries with joins and group by and order by is better suited to regular sql and pdo.

See this good answer:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

And you can use the pdo instance with getPdo().

1 like

jhutto

6 years ago

Level 2

Perfect... Just what I needed. Had to do some searching on getPdo() but got my answer... Here's my final version of the mysql statement...

    $sql = "Select prac.tblpractitioners.id, Concat_Ws(', ', prac.tblpractitioners.LastName, prac.tblpractitioners.Salutation) As PracName,
        prac.tblpractitioners.Availability, prac.tblpractitioners.LastServiceDate, prac.tblpractitioners.ServiceNotes, prac.tblpractitionerphones.PhoneNumber,
        prac.tblphonetypes.Description, qryPreviousMonthService.Indicator, prac.tblpracsuitablepositions.PracDutiesID,
        prac.tblkuppracduties.DutyAbrv, prac.tblkuppracduties.DutyDesc
    From
        prac.tblpractitioners Inner Join
        prac.tblpractitionerphones On prac.tblpractitionerphones.pracid = prac.tblpractitioners.id
                And prac.tblpractitionerphones.PhoneTypeID = prac.tblpractitioners.PrimaryPhoneID Left Join
        prac.tblphonetypes On prac.tblphonetypes.id = prac.tblpractitionerphones.PhoneTypeID Left Join
        (Select
             prac.tblpractitioners.id, prac.tblpractitioners.FirstName, prac.tblpractitioners.LastName, prac.tblservicedutyscheduledprac.pracid,
             prac.tblservicedutyscheduledprac.PRACName, Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%c') As PMonth,
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%Y') As PYear,
             If(prac.tblservicedutyscheduledprac.id Is Null, '', 'X') As Indicator
         From
             prac.tblpractitioners Inner Join
             prac.tblservicedutyscheduledprac On prac.tblservicedutyscheduledprac.pracid = prac.tblpractitioners.id
         Where
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%c') = $PreviousMonth And
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%Y') = $PYear) qryPreviousMonthService On
                qryPreviousMonthService.id = prac.tblpractitioners.id Left Join
        (Select
             prac.tblpractitioners.id, prac.tblpractitioners.FirstName, prac.tblpractitioners.LastName, prac.tblservicedutyscheduledprac.ServiceDate,
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%c') As Month,
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%Y') As Year,
             prac.tblservicedutyscheduledprac.pracid,
             prac.tblservicedutyscheduledprac.PRACName
         From
             prac.tblservicedutyscheduledprac Inner Join
             prac.tblpractitioners On prac.tblservicedutyscheduledprac.pracid = prac.tblpractitioners.id
         Where
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%c') = $Month And
             Date_Format(prac.tblservicedutyscheduledprac.ServiceDate, '%Y') = $Year And
             prac.tblservicedutyscheduledprac.pracid <> 0) qrySuitablePostitionsExcludeSelected On
                qrySuitablePostitionsExcludeSelected.id = prac.tblpractitioners.id Inner Join
        prac.tblpracsuitablepositions On prac.tblpracsuitablepositions.pracid = prac.tblpractitioners.id Inner Join
        prac.tblkuppracduties On prac.tblkuppracduties.id = prac.tblpracsuitablepositions.PracDutiesID
    Where
        prac.tblpractitioners.InService =  $InserviceValue And
        prac.tblpracsuitablepositions.PracDutiesID = $PracDutiesID";    

        
        $result = DB::connection()->getPdo()->query($sql)->fetchAll(\PDO::FETCH_ASSOC);

Took a bit to find the simple pdo line which combined all the commands..

Thank you so much for your direction.

1 like

jlrdw

6 years ago

Level 75

Make sure to bind parameters as necessary.

And remember the db facade can also be used.

just hope all works out for you.

1 like

Please or to participate in this conversation.