Announcement

Collapse
No announcement yet.

[SOLVED]Issue with sc_select_where(add) on a query containing a sub-query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SOLVED]Issue with sc_select_where(add) on a query containing a sub-query

    I have a grid which has the following source query (don't worry, you do not need to go through it in any detail). The point to focus on (I believe) is that:
    1. The query has a sub-query in it, and
    2. The highlighted WHERE clause (see near the bottom) is to be added dynamically only if certain conditions are met.
    The query runs perfectly well - with or without the WHERE - through phpMyAdmin and other SQL utilities.

    Code:
    SELECT
        SUB_ProjName.TimesheetID,
        SUB_ProjName.TimesheetName,
        SUB_ProjName.AgencyID,
        SUB_ProjName.ClientID,
        SUB_ProjName.AgencyContactID,
        SUB_ProjName.ClientContactID,
        SUB_ProjName.WeekCommencing,
        SUB_ProjName.ClientApprovedDate,
        SUB_ProjName.SignedTimesheet,
        SUB_ProjName.User,
        SUB_ProjName.projectname,
        SUB_ProjName.AgencyName,
        SUB_ProjName.ClientName,
        SUB_ProjName.ContactName
    FROM
        (SELECT
            tbltimesheets.TimesheetID,
            tbltimesheets.TimesheetName,
            tbltimesheets.AgencyID,
            tbltimesheets.ClientID,
            tbltimesheets.AgencyContactID,
            tbltimesheets.ClientContactID,
            tbltimesheets.WeekCommencing,
            tbltimesheets.ClientApprovedDate,
            tbltimesheets.SignedTimesheet,
            tbltimesheets.User,
            GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname,
            tblagencies.AgencyName,
            tblclients.ClientName,
            tblclientcontacts.ContactName
         FROM
            tblprojects
            INNER JOIN tblprojecthours
             ON tblprojects.projectid = tblprojecthours.ProjectID
            INNER JOIN tbltimesheets
             ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
            INNER JOIN tblclientcontacts
             ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
            INNER JOIN tblagencies
             ON tbltimesheets.AgencyID = tblagencies.AgencyID
            INNER JOIN tblclients
             ON tbltimesheets.ClientID = tblclients.ClientID
             AND tblclients.ClientID = tblclientcontacts.ClientID
         GROUP BY
            tbltimesheets.TimesheetID) SUB_ProjName
    WHERE
        SUB_ProjName.User = 'testdel'
    ORDER BY
        SUB_ProjName.WeekCommencing DESC
    The bit that determines whether the WHERE is to be included or not is handled in the grid's onScriptInit event, and if it is to be added, I use the following code:

    Code:
    if ({something} == 'this_or_that') {
            sc_select_where(add) = "WHERE SUB_ProjName.User = 'testdel' ";
    }
    When I run the app I get 1054: Unknown column 'SUB_ProjName.User' in 'where clause'.

    When I look at the SQL that SC generates I see (look at the last bit):

    Code:
    (mysqlt): select count(*) from (SELECT SUB_ProjName.TimesheetID, SUB_ProjName.TimesheetName, SUB_ProjName.AgencyID,
     SUB_ProjName.ClientID, SUB_ProjName.AgencyContactID, SUB_ProjName.ClientContactID, SUB_ProjName.WeekCommencing,
     SUB_ProjName.ClientApprovedDate, SUB_ProjName.SignedTimesheet, SUB_ProjName.User, SUB_ProjName.projectname,
     SUB_ProjName.AgencyName, SUB_ProjName.ClientName, SUB_ProjName.ContactName FROM (SELECT tbltimesheets.TimesheetID,
     tbltimesheets.TimesheetName, tbltimesheets.AgencyID, tbltimesheets.ClientID, tbltimesheets.AgencyContactID, tbltimesheets.ClientContactID,
     tbltimesheets.WeekCommencing, tbltimesheets.ClientApprovedDate, tbltimesheets.SignedTimesheet, tbltimesheets.User, GROUP_CONCAT(
     projectname SEPARATOR ' ' ) AS projectname, tblagencies.AgencyName, tblclients.ClientName, tblclientcontacts.ContactName FROM tblprojects
     INNER JOIN tblprojecthours ON tblprojects.projectid = tblprojecthours.ProjectID INNER JOIN tbltimesheets ON tblprojecthours.TimesheetID =
     tbltimesheets.TimesheetID INNER JOIN tblclientcontacts ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID INNER JOIN
     tblagencies ON tbltimesheets.AgencyID = tblagencies.AgencyID INNER JOIN tblclients ON tbltimesheets.ClientID = tblclients.ClientID AND
     tblclients.ClientID = tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName ) nm_sel_esp WHERE SUB_ProjName.User
     = 'testdel'
    It seems to me that the query is fine except for the red bit that SC adds near the end. I am only guessing here, but it looks like SC wants nm_sel_esp at the very END of the query - so that the last bit should look like this (with the close bracket moved too):

    Code:
    ......... tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName WHERE SUB_ProjName.User = 'testdel' ) nm_sel_esp
    If that's the case then SC has miscalculated where the query ends. And I think its the sub-query that's confused the software. Is this a bug, or am I doing something stupid?

    Thoughts folks?
    Last edited by adz1111; 05-20-2015, 12:34 PM.

  • #2
    I would use a slightly different method..

    SELECT
    SUB_ProjName.TimesheetID,
    SUB_ProjName.TimesheetName,
    SUB_ProjName.AgencyID,
    SUB_ProjName.ClientID,
    SUB_ProjName.AgencyContactID,
    SUB_ProjName.ClientContactID,
    SUB_ProjName.WeekCommencing,
    SUB_ProjName.ClientApprovedDate,
    SUB_ProjName.SignedTimesheet,
    SUB_ProjName.User,
    SUB_ProjName.projectname,
    SUB_ProjName.AgencyName,
    SUB_ProjName.ClientName,
    SUB_ProjName.ContactName
    FROM
    (SELECT
    tbltimesheets.TimesheetID,
    tbltimesheets.TimesheetName,
    tbltimesheets.AgencyID,
    tbltimesheets.ClientID,
    tbltimesheets.AgencyContactID,
    tbltimesheets.ClientContactID,
    tbltimesheets.WeekCommencing,
    tbltimesheets.ClientApprovedDate,
    tbltimesheets.SignedTimesheet,
    tbltimesheets.User,
    GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname,
    tblagencies.AgencyName,
    tblclients.ClientName,
    tblclientcontacts.ContactName
    FROM
    tblprojects
    INNER JOIN tblprojecthours
    ON tblprojects.projectid = tblprojecthours.ProjectID
    INNER JOIN tbltimesheets
    ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
    INNER JOIN tblclientcontacts
    ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
    INNER JOIN tblagencies
    ON tbltimesheets.AgencyID = tblagencies.AgencyID
    INNER JOIN tblclients
    ON tbltimesheets.ClientID = tblclients.ClientID
    AND tblclients.ClientID = tblclientcontacts.ClientID
    GROUP BY
    tbltimesheets.TimesheetID) SUB_ProjName
    WHERE
    1=1 and SUB_ProjName.User = 'testdel'
    ORDER BY
    SUB_ProjName.WeekCommencing DESC


    Then adding the rest would be simpler..
    Lets hope that works for you...

    Comment


    • #3
      Thanks rr

      Unfortunately, I tried that and it makes no difference I'm afraid.

      This was the error:

      Code:
      Error
      Error while accessing the database:
      Unknown column 'SUB_ProjName.User' in 'where clause'
      select count(*) from (SELECT SUB_ProjName.TimesheetID, SUB_ProjName.TimesheetName, SUB_ProjName.AgencyID, SUB_ProjName.ClientID, SUB_ProjName.AgencyContactID, SUB_ProjName.ClientContactID, SUB_ProjName.WeekCommencing, SUB_ProjName.ClientApprovedDate, SUB_ProjName.SignedTimesheet, SUB_ProjName.User, SUB_ProjName.projectname, SUB_ProjName.AgencyName, SUB_ProjName.ClientName, SUB_ProjName.ContactName FROM (SELECT tbltimesheets.TimesheetID, tbltimesheets.TimesheetName, tbltimesheets.AgencyID, tbltimesheets.ClientID, tbltimesheets.AgencyContactID, tbltimesheets.ClientContactID, tbltimesheets.WeekCommencing, tbltimesheets.ClientApprovedDate, tbltimesheets.SignedTimesheet, tbltimesheets.User, GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname, tblagencies.AgencyName, tblclients.ClientName, tblclientcontacts.ContactName FROM tblprojects INNER JOIN tblprojecthours ON tblprojects.projectid = tblprojecthours.ProjectID INNER JOIN tbltimesheets ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID INNER JOIN tblclientcontacts ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID INNER JOIN tblagencies ON tbltimesheets.AgencyID = tblagencies.AgencyID INNER JOIN tblclients ON tbltimesheets.ClientID = tblclients.ClientID AND tblclients.ClientID = tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName ) nm_sel_esp WHERE 1=1 AND SUB_ProjName.User = 'testdel'
      Again, the entire WHERE clause (regardless of what's in it) is placed AFTER the end of the query as far as SC is concerned. I'm going to hack the generated code later to see if it works with the query string changed so the ") nm_sel_esp" is last in the string, AFTER the WHERE.

      I'll post back soon.
      Last edited by adz1111; 05-21-2015, 05:49 AM. Reason: added error msg

      Comment


      • #4
        You could also create a view for the inner query and then test it with that setup.

        Comment


        • #5
          Yeah - that's the next bit I'll try as moving the ") nm..." made no difference. That's because I thought the "nm_sel_esp" was a variable they put in, but when looking at the fully built query I can see its just the name they give to their own wrapper subquery. So the query that's failing is not actually the one that feeds the grid, but a pre-query SC runs for SC to determine the record count my query will produce (presumably for the navigation bits on the grid). Their full query is:

          Code:
          select 
          	count(*) 
          from 
          	(SELECT 
          		SUB_ProjName.TimesheetID, 
          		SUB_ProjName.TimesheetName, 
          		SUB_ProjName.AgencyID, 
          		SUB_ProjName.ClientID, 
          		SUB_ProjName.AgencyContactID, 
          		SUB_ProjName.ClientContactID, 
          		SUB_ProjName.WeekCommencing, 
          		SUB_ProjName.ClientApprovedDate, 
          		SUB_ProjName.SignedTimesheet, 
          		SUB_ProjName.User, 
          		SUB_ProjName.projectname, 
          		SUB_ProjName.AgencyName, 
          		SUB_ProjName.ClientName, 
          		SUB_ProjName.ContactName 
          	FROM 
          		(SELECT 
          			tbltimesheets.TimesheetID, 
          			tbltimesheets.TimesheetName, 
          			tbltimesheets.AgencyID, 
          			tbltimesheets.ClientID, 
          			tbltimesheets.AgencyContactID, 
          			tbltimesheets.ClientContactID, 
          			tbltimesheets.WeekCommencing, 
          			tbltimesheets.ClientApprovedDate, 
          			tbltimesheets.SignedTimesheet, 
          			tbltimesheets.User, 
          			GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname, 
          			tblagencies.AgencyName, 
          			tblclients.ClientName, 
          			tblclientcontacts.ContactName 
          		FROM 
          			tblprojects 
          			INNER JOIN tblprojecthours 
          				ON tblprojects.projectid = tblprojecthours.ProjectID 
          			INNER JOIN tbltimesheets 
          				ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID 
          			INNER JOIN tblclientcontacts 
          				ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID 
          			INNER JOIN tblagencies 
          				ON tbltimesheets.AgencyID = tblagencies.AgencyID 
          			INNER JOIN tblclients 
          				ON tbltimesheets.ClientID = tblclients.ClientID AND tblclients.ClientID = tblclientcontacts.ClientID 
          			GROUP BY tbltimesheets.TimesheetID
          		) SUB_ProjName 
                   ) nm_sel_esp	
          WHERE
                  SUB_ProjName.User = 'testdel'
          Clearly that will fail as the WHERE is in the wrong place. If I move it as I suggested to the right place it works in phpMyAdmin - but not in the hacked generated code :-(

          UPDATE: Realised moving to the end does work but then there is another SC pre-query that takes the corrected query and adds an ORDER BY to it so once again the nm_sel_esp is not at the very end again - so that query then fails. Looks like a view is the way to go (hoping not to take too much of a performance hit)... will update here later
          Last edited by adz1111; 05-21-2015, 06:55 AM.

          Comment


          • #6
            [SOLVED] Issue with sc_select_where(add) on a query containing a sub-query

            SOLVED

            Ok - as Views don't allow sub-queries, by rewriting the query so it used joins rather than sub-queries, I simply tried that rewritten query (before making it a view), and it worked like that, so no view was necessary. So the new query became (without the WHERE - but the WHERE is now happily dynamically added as needed to that SQL with the sc_select_where(add) macro):

            Code:
            SELECT
            	tbltimesheets.TimesheetID,
            	tbltimesheets.TimesheetName,
            	tbltimesheets.WeekCommencing,
            	tbltimesheets.AgencyID,
            	tblagencies.AgencyName,
            	tbltimesheets.AgencyContactID,
            	tblagencycontacts.ContactName,
            	tbltimesheets.ClientID,
            	tblclients.ClientName,
            	tbltimesheets.ClientContactID,
            	tblclientcontacts.ContactName,
            	tbltimesheets.ClientApprovedDate,
            	tbltimesheets.SignedTimesheet,
            	tbltimesheets.User,
            	GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname
            FROM
            	tblprojects
            	INNER JOIN tblprojecthours
            	 ON tblprojects.projectid = tblprojecthours.ProjectID
            	INNER JOIN tbltimesheets
            	 ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
            	INNER JOIN tblagencycontacts
            	 ON tbltimesheets.AgencyContactID = tblagencycontacts.AgencyContactID
            	INNER JOIN tblclientcontacts
            	 ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
            	INNER JOIN tblclients
            	 ON tblclientcontacts.ClientID = tblclients.ClientID
            	INNER JOIN tblagencies
            	 ON tblagencycontacts.AgencyID = tblagencies.AgencyID
            GROUP BY
            	tbltimesheets.TimesheetID
            ORDER BY
            	tbltimesheets.WeekCommencing DESC

            Comment

            Working...
            X