Paginable::TemplatesController#organisational crashes when parameter `search` is given.

[nicolasfranck]

Please complete the following fields as applicable:

What version of the DMPRoadmap code are you running? (e.g. v2.2.0)

3.1.0 and before

Actual behaviour:

When list of organisational templates on the org admin is long enough to display a search bar, and I type a search term, the results are never shown. A console error, and the logs server side show that the query has crashed (mysql):

Mysql2::Error: Unknown column 'templates.org_id' in 'on clause': SELECT  DISTINCT `templates`.* FROM (SELECT MAX(version) AS version, `templates`.`family_id` FROM `templates` WHERE `templates`.`archived` = FALSE AND `templates`.`family_id` IN (18, 19, 23, 24, 131, 171, 181, 191, 331, 530322321, 1150190389, 1305794772, 2133032205) GROUP BY `templates`.`family_id`) current INNER JOIN `orgs` `orgs_templates` ON `orgs_templates`.`id` = `templates`.`org_id` INNER JOIN templates ON current.version = templates.version
  AND current.family_id = templates.family_id
INNER JOIN orgs ON orgs.id = templates.org_id WHERE `templates`.`archived` = FALSE AND `templates`.`customization_of` IS NULL AND `templates`.`org_id` = 4 AND `templates`.`archived` = FALSE AND (lower(templates.title) LIKE lower('%avg%') OR lower(orgs.name) LIKE lower('%avg%')) ORDER BY `title` ASC LIMIT 100 OFFSET 0

There seems to be a problem with the order of the inner join. If I put the INNER JOIN orgs orgs_templates AFTER INNER JOIN templates ON current.version = templates.version then it starts working. Logically templates.org_id cannot exist because the result before is marked as current and that only contains family_id and version

If scope Template#search isn't called, a correct query is formed. I cannot find the exact reason for this behaviour.

Apparently this was already present in 3.0.6, and probably before also.

Steps to reproduce:

• goto http://localhost:3000/paginable/templates/organisational/1?utf8=%E2%9C%93&search=avg&commit=Search

[briri]

Good catch @nicolasfranck. I can confirm that I am seeing this error as well. It has probably been there for some time and no one has reported it.

[nicolasfranck]

something to do with the sql alias current that is introduced in one of the Template scopes..