Modified DF_Model's join_to() method to take account of a supplied join alias…

What I did

• Modified DF_Model's join_to() method to take account of a supplied join alias when preparing the join condition.

Implications

None.

Setup

None.

How to test

• Code review
• Set up a test using a join alias and confirm that it now works.
• Example case: commit 3af37e0b4eedf44a6a8eeb52ce54614cc1897db6in Pulse, release/invoicing_workflow branch, when creating a new purchase order.
  • Entering a value in the Job Name & Number field would generate an error as it did autosuggestion lookups.
  • The join alias in this case is specified on line 377 of projects_model.
  • In dev, the API method response contained this incorrect SQL:

SELECT SQL_CALC_FOUND_ROWS null AS `__FOUND_ROWS__`, `projects`.`id`, `projects`.`client_id`, `projects`.`project_leader_id`, `projects`.`inbox_project_area_id`, `projects`.`job_number`, `projects`.`name`, `projects`.`description`, `projects`.`type`, `projects`.`job_rate`, `projects`.`expected_start_date`, `projects`.`agreed_hours_budget`, `projects`.`avatar_path`, `projects`.`job_source`, `projects`.`job_initiator`, `projects`.`job_author`, `projects`.`job_studio`, `projects`.`job_is_existing_client`, `projects`.`contract_signed`, `projects`.`medical_project`, `projects`.`created`, `projects`.`modified`, `projects`.`created_by_id`, `projects`.`modified_by_id`, `projects`.`status`, `projects`.`purchase_order_number`, `clients`.`id` AS _j_clients_id, `clients`.`client_code` AS _j_clients_client_code, `clients`.`name` AS _j_clients_name, `clients`.`known_as` AS _j_clients_known_as, `clients`.`status` AS _j_clients_status, `clients`.`payment_terms_days` AS _j_clients_payment_terms_days, `clients`.`email` AS _j_clients_email, `clients`.`phone` AS _j_clients_phone, `clients`.`website` AS _j_clients_website, `clients`.`invoice_currency_id` AS _j_clients_invoice_currency_id, `clients`.`invoice_tax_rate_percent` AS _j_clients_invoice_tax_rate_percent, `clients`.`is_address_same` AS _j_clients_is_address_same, `clients`.`is_internal` AS _j_clients_is_internal, `clients`.`crm_contact_id` AS _j_clients_crm_contact_id, `clients`.`xero_contact_number` AS _j_clients_xero_contact_number, `clients`.`branch_id` AS _j_clients_branch_id, `clients`.`created_by_id` AS _j_clients_created_by_id, `clients`.`created` AS _j_clients_created, `clients`.`modified_by_id` AS _j_clients_modified_by_id, `clients`.`modified` AS _j_clients_modified
FROM (`projects`)
INNER JOIN `clients` AS `_clients_` ON `clients`.`id`=`projects`.`client_id`
LEFT JOIN `dandi_expenses` ON `dandi_expenses`.`job_id`=`projects`.`id`
LEFT JOIN `dandi_expense_items` ON `dandi_expense_items`.`id`=`dandi_expenses`.`expense_item_id`
INNER JOIN `clients` ON `projects`.`client_id` = `clients`.`id`
WHERE CONCAT_WS( ' ', `_clients_`.`client_code`, `projects`.`job_number`, `projects`.`name`, `dandi_expense_items`.`description` ) LIKE '%123%'
AND `projects`.`status` IN ('open', 'deleted') 
GROUP BY `projects`.`id`
ORDER BY `projects`.`created` DESC
LIMIT 15

The issue was that the join that used the _clients_ table alias did not use that alias in the left hand side of that join's condition. Correct SQL, after this patch, has:

…
FROM (`projects`)
INNER JOIN `clients` AS `_clients_` ON `_clients_`.`id`=`projects`.`client_id`
…