Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts

    order by field from related table with 'IN' clause?

    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

    Code:
    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?)
    Anyone?

  • #2
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Try something like

    Code:
    SELECT *  -- I would really explicitly name the columns rather than using asterik
    FROM inspections
    INNER JOIN orders
    ON (orders.id = inspections.order_id)
    WHERE inspections.inspector_id = '450f379a-f9d2-dd2c-b1e0-44b31816b41c' 
    ORDER BY order.order_no
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #3
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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.
    PHP Code:
    <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...
    PHP Code:
    <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.

    PHP Code:
        /* 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.
            
    if($custom_join){
                
    $query .= $custom_join['select'];
            }

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

            
    //Add custom fields join condition.
            
    if($custom_join){
                
    $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;
            else
                
    $query .= "where ".$where_auto;

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

            return 
    $query;
        } 
    Last edited by fuzzy1; 03-16-2008 at 12:29 PM. Reason: replaced 'last_name' with 'order_no'

  • #4
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    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.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •