how to change the order of columns in a query with joins using Zend_Db_Select

Question

I have rather complex query and I could build it with Zend_Db_Select step by step. The query look likes this:

SELECT `subscribers`.`subscriber_id`, `subscribers`.`email_address`, `subscribers`.`first_name`,
`subscribers`.`last_name`, `t1`.`value` AS `'SingleLineField'`, `t2`.`value` AS `'MultilineField'`,
`t3`.`value` AS `'CheckboxField'`
FROM `subscribers`
INNER JOIN (SELECT subscriber_id
            FROM subscribers
            WHERE list_id =68 AND state=1
            LIMIT 0, 25) AS `s` ON subscribers.subscriber_id = s.subscriber_id
LEFT JOIN `subscribers_custom_data_string` AS `t1` ON subscribers.subscriber_id=t1.subscriber_id AND t1.field_id=177
LEFT JOIN `subscribers_custom_data_string` AS `t2` ON subscribers.subscriber_id=t2.subscriber_id AND t2.field_id=178
LEFT JOIN (SELECT subscribers_multivalued.subscriber_id, field_id, GROUP_CONCAT(value SEPARATOR ',') AS value
           FROM subscribers_multivalued
           INNER JOIN (SELECT subscriber_id
                       FROM subscribers
                       WHERE list_id =68 AND state=1
                       LIMIT 0, 25) AS s1 ON subscribers_multivalued.subscriber_id=s1.subscriber_id
           WHERE field_id=179
           GROUP BY subscribers_multivalued.subscriber_id, field_id) AS `t3` ON subscribers.subscriber_id=t3.subscriber_id AND t3.field_id=179

But now I need to be able to dynamically change the order of columns, to be able transform aforementioned queries to something like:

SELECT  `subscribers`.`subscriber_id`, `subscribers`.`email_address` , `t1`.`value` AS `'SingleLineField'`, `t2`.`value` AS `'MultilineField'`,
`t3`.`value` AS `'CheckboxField'`, `subscribers`.`first_name`,
`subscribers`.`last_name`
FROM `subscribers`
INNER JOIN (SELECT subscriber_id
            FROM subscribers
            WHERE list_id =68 AND state=1
            LIMIT 0, 25) AS `s` ON subscribers.subscriber_id = s.subscriber_id
LEFT JOIN `subscribers_custom_data_string` AS `t1` ON subscribers.subscriber_id=t1.subscriber_id AND t1.field_id=177
LEFT JOIN `subscribers_custom_data_string` AS `t2` ON subscribers.subscriber_id=t2.subscriber_id AND t2.field_id=178
LEFT JOIN (SELECT subscribers_multivalued.subscriber_id, field_id, GROUP_CONCAT(value SEPARATOR ',') AS value
           FROM subscribers_multivalued
           INNER JOIN (SELECT subscriber_id
                       FROM subscribers
                       WHERE list_id =68 AND state=1
                       LIMIT 0, 25) AS s1 ON subscribers_multivalued.subscriber_id=s1.subscriber_id
           WHERE field_id=179
           GROUP BY subscribers_multivalued.subscriber_id, field_id) AS `t3` ON subscribers.subscriber_id=t3.subscriber_id AND t3.field_id=179                         

Or for example, something like this one:

SELECT  `subscribers`.`subscriber_id`, `t1`.`value` AS `'SingleLineField'`, `t2`.`value` AS `'MultilineField'`,
`t3`.`value` AS `'CheckboxField'`, `subscribers`.`email_address`, `subscribers`.`first_name`,
`subscribers`.`last_name`
FROM `subscribers`
INNER JOIN (SELECT subscriber_id
            FROM subscribers
            WHERE list_id =68 AND state=1
            LIMIT 0, 25) AS `s` ON subscribers.subscriber_id = s.subscriber_id
LEFT JOIN `subscribers_custom_data_string` AS `t1` ON subscribers.subscriber_id=t1.subscriber_id AND t1.field_id=177
LEFT JOIN `subscribers_custom_data_string` AS `t2` ON subscribers.subscriber_id=t2.subscriber_id AND t2.field_id=178
LEFT JOIN (SELECT subscribers_multivalued.subscriber_id, field_id, GROUP_CONCAT(value SEPARATOR ',') AS value
           FROM subscribers_multivalued
           INNER JOIN (SELECT subscriber_id
                       FROM subscribers
                       WHERE list_id =68 AND state=1
                       LIMIT 0, 25) AS s1 ON subscribers_multivalued.subscriber_id=s1.subscriber_id
           WHERE field_id=179
           GROUP BY subscribers_multivalued.subscriber_id, field_id) AS `t3` ON subscribers.subscriber_id=t3.subscriber_id AND t3.field_id=179                 

The problem is that I can't use method from of Zend_Db_Select multiple times (it will generate new join in this case which is not what I want) to change the order of email, first and last names fields in the query. Do you have any ideas how to solve this problem?

Answer 1

You can use the ->columns() method to choose the columns you want. Us empty arrays in the from methods, then use columns() calls, even with a big Zend_Db_Expr inside, where you can decide to write your select arguments in the way and order you want.