SOLVED Error 1054 after upgrade

[ontarget]

Hi just upgraded to j3.6.2 and Fabrik 3.5
I have a form which was working fine now after the upgrade getting error -
I installed Fabrik Master from github and ran update DB on the forms cleared joomla and browser cache - any ideas please?
1054
Error: 1054
Unknown column 'multi_assign_repeat_ms_registrant.last_name' in 'order clause' SQL=SELECT `multi_assign_repeat_ms_registrant`.id AS id,`multi_assign_repeat_ms_registrant`.parent_id, `multi_assign_repeat_ms_registrant`.`ms_registrant` AS value, CONCAT_WS('', `user_registration`.last_name, ', ', `user_registration`.first_name, ', ', `user_registration`.tcn) AS text FROM `multi_assign_repeat_ms_registrant` LEFT JOIN user_registration ON `user_registration`.`id` = `multi_assign_repeat_ms_registrant`.`ms_registrant` ORDER BY CONCAT_WS('', `multi_assign_repeat_ms_registrant`.last_name, ', ', `multi_assign_repeat_ms_registrant`.first_name, ', ', `multi_assign_repeat_ms_registrant`.tcn)ASC

 

[cheesegrits]

Looks like a join element query.

Do you have a join element with a "Where filter" that adds an ORDER BY clause?

-- hugh

 

[ontarget]

Thanks Hugh
Yes have a few databasejoin elements and cascadingdropdowns - based on the error is it possible to tell which is the offending element? I think it's the element ms_registrant (cascadingDD) as there is a
concat label:
{thistable}.last_name, ', ', {thistable}.first_name, ', ',
{thistable}.tcn

Then in the advanced tab
WHERE QUERY

{thistable}.id NOT IN (SELECT `registrant` FROM `course_registrants` WHERE `course` = '{multi_assign___ms_course_raw}')
AND {thistable}.id IN (SELECT `parent_id` FROM `user_registration_repeat_user_teams` WHERE `user_teams` = '{multi_assign___ms_category_raw}')

EVAL OPTIONS:

$app = JFactory::getApplication();
$office_hours = $app->input->getString('multi_assign___work_hours');
$course_units = $app->input->getString('multi_assign___course_units');
$max_untis_left = 40 - $course_units;
//echo $office_hours;

$year = (date("n") < 9) ? date("Y") - 1 : date("Y");
$startDate = $year."-09-01";
$phpdate = strtotime( $startDate );
$mysqldate = date( 'Y-m-d', $phpdate );

$yes = $db->quote('YES');

if($office_hours == 'YES') {
  $db =& JFactory::getDBO();
  //AND cr.course_date > '2015-09-01'
  // INNER JOIN `courses` AS c ON ur.course = c.id
  //      AND c.course_date > '$mysqldate'
  $query = "
      SELECT cr.registrant, SUM( cr.units ) AS total, SUM(cr.units_saved) AS saved, ur.days
      FROM `course_registrants` AS cr
      INNER JOIN `user_registration` AS ur ON ur.id = cr.registrant
      INNER JOIN `courses` AS c ON c.id = cr.course 
      WHERE cr.office_hours = $yes
      AND c.course_date > '$mysqldate'
      GROUP BY cr.registrant
    ";  
  $db->setQuery($query);
  $rows = $db->loadObjectList(); 
  foreach ($rows as $row) {
    if (!isset($row->days)) $row->days = 0;
    $enough_untis_left = ($row->days)*2 + $row->saved - $row->total - $course_units;
    // remove users from cdd
    if ($opt->value == $row->registrant && $enough_untis_left < 0) return false; 
    //if ($opt->value == $row->registrant) $opt->text = $opt->text." used-".$row->total." left-".$enough_untis_left;
  }
} else { 
  $db =& JFactory::getDBO();
  //WHERE cr.course_date > '2015-09-01'
  $query = "
    SELECT cr.registrant 
    FROM `course_registrants` AS cr
    INNER JOIN `courses` AS c ON c.id = cr.course 
    WHERE c.course_date > '$mysqldate'
    GROUP BY cr.registrant 
    HAVING (SUM(cr.units) - SUM(cr.units_saved)) > $max_untis_left 
  ";
  $db->setQuery($query);
  $rows = $db->loadObjectList(); 
  foreach ($rows as $row) {
    // remove users from cdd
   if ($opt->value == $row->registrant) return false;
  }
}

 

[ontarget]

Hi Hugh
I reinstalled the project upgrading joomla 3.6.2 first then leaving fabrik on 3.4.1 - there was no 1054 error and the project works fine.
After upgrading fabrik to 3.5 by unzipping the master file from github the error appears. Is it a syntax error in the sql statements e.g I have seen some forum posts stating use of " " instead of ' ' see http://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query?
Thanks

 

[troester]

Pretty sure "ORDER BY CONCAT_WS('', `multi_assign_repeat_ms_registrant`.last_name," should be "`user_registration`.last_name"

I think in the ORDER BY in multiselects it's replacing the wrong {thistable} (taking the yyy-repeat-xxx instead of the original one the dbjoin is going to.
I can't see an ORDER BY in your advanced Joins-where.
Do you have also a Filter-where set? or it's coming from an other element?

I think I've seen this also http://fabrikar.com/forums/index.php?threads/filter-order-of-multiselect-dbjoin-element.44968

At some point of testing I've seen something like "FROM dbjointable as repeat-table" which seems to be missing at some other place

Are you running the latest GitHub (including this commit?)
Attempting to get ORDER BY working in join element 'filter where'

 

[cheesegrits]

I think in the ORDER BY in multiselects it's replacing the wrong {thistable} (taking the yyy-repeat-xxx instead of the original one the dbjoin is going to.

Yeah, I think you are right. Looking at it now.

-- hugh

 

[cheesegrits]

OK, I can see where that query is being built, but I can't provoke the error.

Can someone give me the exact set up to tickle this error, in the simplest way possible, and I'll set up a test form for it.

The tables I usually use for CDD testing is states / cites.

I have a test setup which has checkboxes for both states and cities, but even with CONCAT labels on both elements, and using checkbox filters on the list for both, it isn't hitting the error in list or form display.

-- hugh

 

[troester]

Checkbox dbjoin
List view settings/Filters: Type=dbjoin, Order by = label
Some concat with {thistable}
==> error 1054 in list view

 

[ontarget]

Hi thanks for looking into this - if it helps you I can give you access to the project we are working on?

 

[ontarget]

Are you running the latest GitHub (including this commit?)
Attempting to get ORDER BY working in join element 'filter where'

Hi Troester - just to confirm I have uploaded and extracted a zip of the last commit from Git this morning and tested but the 1054 error remains.
thanks

 

[ontarget]

I have put the access details in the Fabrik "My Sites" section if it helps you find the source of the error

 

[cheesegrits]

Try this ...

https://github.com/Fabrik/fabrik/commit/97726efd5892bf56b6b6c81f86e43a82305d4456

-- hugh

 

[ontarget]

Thanks Hugh
Yes your last commit has fixed the 1054 error - however Cascading Dropdowns are not pulling any data any longer.
When the "Category" is selected it should populate the "Course Title" DD
See screen shots based on Test site vs live

Test site (Fabrik 3.5 with Hugh's databasejoin plugin commit - course title is a cascadingDD)

Live Site (Fabrik 3.4.1)

 

[cheesegrits]

*sigh*

OK, I'm setting up a simple test case.

Speaking of test cases, the login for the test site you provided doesn't work.

-- hugh

 

[cheesegrits]

I can't replicate this. All my CDD tests are working.

Can you either sort out the login for your test site, or give me the minimal setup I need to replicate this problem.

I have a join and a CDD, but set as dropdowns (as that seems to be what you pictures depict). I have "concat labels" on both, and have tried with various permutations of filters )checkbox, dropdown, ordering, etc).

-- hugh

 

[ontarget]

Thanks Hugh 7am here! Sorry sorted the login for you

 

[ontarget]

I can't replicate this. All my CDD tests are working.

Can you either sort out the login for your test site, or give me the minimal setup I need to replicate this problem.

I have a join and a CDD, but set as dropdowns (as that seems to be what you pictures depict). I have "concat labels" on both, and have tried with various permutations of filters )checkbox, dropdown, ordering, etc).

-- hugh

My bad - the CDD are working now it was a caching issue that was blocking the Ajax call - can i buy you a beer!

 

[cheesegrits]

Well, you could get a month of Standard support for $20 (or whatever it is).

-- hugh