View Full Version : order by field from related table with 'IN' clause?

03-15-2008, 09:03 PM
Hey All,

It's been a while, and my SQL query skills have gotten rusty (not that they were ever all that polished ;-).

I'm trying to see my way to ordering results on basis of field values in a related table.
Something like

SELECT inspections.* FROM inspections
WHERE inspections.inspector_id = '450f379a-f9d2-dd2c-b1e0-44b31816b41c'
AND (inspections.deleted=0)
ORDER BY order_no
IN (SELECT * FROM orders WHERE orders.id = inspections.order_id)
Which of course doesn't work!
Any solution MUST come AFTER the ORDER BY clause insofar as it will be passed onclick to a dynamic $order_by= variable in PHP.
(this is the list view of a Sugar CRM application module and --- Jeez I hope that makes sense?)

03-16-2008, 12:52 AM
Try something like

SELECT * -- I would really explicitly name the columns rather than using asterik
FROM inspections
ON (orders.id = inspections.order_id)
WHERE inspections.inspector_id = '450f379a-f9d2-dd2c-b1e0-44b31816b41c'
ORDER BY order.order_no

03-16-2008, 01:58 AM
Thanks StupidRalph!

I was afraid I might fail to articulate my plight, and rightly so -- it seems.

My aim is to avoid endevoring to unravel the deep tangle of SugarCRM code which results in the first part of the originally posted Query:

SELECT inspections.* FROM inspections
WHERE inspections.inspector_id = '450f379a-f9d2-dd2c-b1e0-44b31816b41c'
AND (inspections.deleted=0) (asterisk and all =)

As coded by the good folks at SugarCRM, the header row of each module's list_view.htm page ( for contacts, accounts, appointments etc) offers Order By or Sort options by column name.
<td><a href="{ORDER_BY}order_no">{MOD.LBL_LIST_ORDER_NO}{arrow_start}{order_no_arrow}{arrow_end}</a></td>... wherein order_no is an actual column in the modules corrisponding table, and the value order_no is passed to the variable $order_by in each modules root.php (in this case inspections.php).

In this case order_no is NOT a column in the `inspections` table, but is rather a column in the related table `orders`automagically returned or printed in the inspections module's list_view.htm
As such...
<td><a href="{ORDER_BY}order_no">{MOD.LBL_LIST_ORDER_NO}{arrow_start}{order_no_arrow}{arrow_end}</a></td>... does'nt work as order_no is not a column in the inspections table -- even though inspections/list_view.htm prints the values for order_no from the orders table via the relationship table orders_inspections.

My hope is to solve for $order_by = '?' without having to reinvent the the entire achetecture of the modules list view which follows -- just for grins.

/* This method is used to generate query for the list form. The base implementation of this method
* uses the table_name and list_field varaible to generate the basic query and then adds the custom field
* join and team filter. If you are implementing this function do not forget to consider the additional conditions.
function create_list_query($order_by, $where)
//Build the join condition for custom fields, the custom field array was populated
//when you invoked the constructor for the SugarBean.
$custom_join = $this->custom_fields->getJOIN();

//Build the select list for the query.
$query = "SELECT ";
$query .= " inspections.* ";

//If custom fields exist append the select list here.
$query .= $custom_join['select'];

//append the WHERE clause to the $query string.
$query .= " FROM inspections ";

//Add custom fields join condition.
$query .= $custom_join['join'];

//Append additional filter conditions.
$where_auto = " (inspections.deleted=0)";

//if the function recevied a where clause append it.
if($where != "")
$query .= "where $where AND ".$where_auto;
$query .= "where ".$where_auto;

//append the order by clause.
echo $order_by;
//$order_by = "";
if($order_by != "")
$query .= " ORDER BY $order_by";
$query .= " ORDER BY 'status DESC'";

return $query;

03-17-2008, 08:14 AM
I'm sorry. I just can't wrap my head around it either. Does the inspection table not have a foreign key for the orders table? It would appear it would b/c of your initial post. You effectively are performing an INNER JOIN in your WHERE clause.