0

I have static query (db_query works great) but for some reason, I have to put a condition to the query result

I don't want to re-write the same db_query with "where", I'm looking for a way to write just the condition part and avoid to re-write all the query.

So I want to add a condition (that is for dynamic query) to a static query

Is there any solution ?

Here is one example on my code :

$resultat = db_query(
    "SELECT `field_titre_en_arabe_value`, `field_etat_du_livre_value`, e.denomination_ed, entity_id, `field_prix_value`, `name` 
    FROM (
        SELECT `nid`, ta.entity_id,  n.created, `field_titre_en_arabe_value`, `field_etat_du_livre_value`,`field_prix_value`,`name`, n.uid, edi.field_edit_target_id, COALESCE (edi.field_edit_target_id, n.uid) as newid 
        FROM {node} as n
            left join {field_data_field_etat_du_livre} as et on n.nid = et.entity_id
            left join {field_data_field_titre_en_arabe} as ta on n.nid = ta.entity_id
            left join {field_data_field_edit} as edi on n.nid = edi.entity_id
            left join {field_data_field_prix} as pr on n.nid = pr.entity_id
            left join {field_data_field_categorie} as cat on n.nid = cat.entity_id
            left join {taxonomy_term_data} as tax on cat.field_categorie_tid = tax.tid
        ) as tabb 
        left join {editeur} as e on e.uid = tabb.newid
        order by  field_etat_du_livre_value asc, created desc"
);

Now here is the condition I was talking about, and look at the 'where' part, it came after I re-write the same first query, I want to write only the "where" part after the if

if (isset($form_state['storage']['etat_livre'])){
    $resultat = db_query(
        "SELECT `field_titre_en_arabe_value`, `field_etat_du_livre_value`, e.denomination_ed, entity_id, `field_prix_value`, `name`
        FROM (SELECT `nid`, ta.entity_id,  n.created, `field_titre_en_arabe_value`, `field_etat_du_livre_value`,`field_prix_value`,`name`, n.uid, edi.field_edit_target_id, COALESCE (edi.field_edit_target_id, n.uid) as newid 
            FROM {node} as n
                left join {field_data_field_etat_du_livre} as et on n.nid = et.entity_id
                left join {field_data_field_titre_en_arabe} as ta on n.nid = ta.entity_id
                left join {field_data_field_edit} as edi on n.nid = edi.entity_id
                left join {field_data_field_prix} as pr on n.nid = pr.entity_id
                left join {field_data_field_categorie} as cat on n.nid = cat.entity_id
                left join {taxonomy_term_data} as tax on cat.field_categorie_tid = tax.tid
        ) as tabb 
        left join {editeur} as e on e.uid = tabb.newid  
        where field_etat_du_livre_value = :field_etat_du_livre_value 
        order by field_etat_du_livre_value asc, created desc", array(
            ':field_etat_du_livre_value' => $form_state['values']['etat_livre']                
        )
    );

As it is now, it works, but i think the code is redundant is there any way to avoid that please ?

1

Since you are querying some tables to get the value of an entity field, I would rather use the EntityFieldQuery class.

To answer your question, what you want to achieve can be obtained by concatenating strings to the the query string.

$query = "The first part of the query";
$args = array();

if (isset($form_state['storage']['etat_livre'])) {
  $query .= "WHERE field_etat_du_livre_value = :field_etat_du_livre_value";
  $args[':field_etat_du_livre_value'] = $form_state['storage']['etat_livre'];
}

$query .= "ORDER BY field_etat_du_livre_value ASC, created DESC";

$result = db_query($query, $args);

As side note:

  • The query should not use back-ticks
  • The reserved words should be written in upper case
  • Thanks, I understand that, but in my case, I have db_query(SELECT a from {tableA}) after that the result is on an array (and set on a tableselect) now i have a dropdown list select on my form that's why i have to filter the tableselect, to filter = to put a condition in my query and set $form_state['values']['select'] on the condition to get a filtered tableselect. the problem is that i don't know how to write the condition – Mitch Jul 14 '15 at 13:00
  • 1
    Edit your question, and show the code you are using. If you are doing two separate queries, what I wrote doesn't change: The WHERE part is always added in the same way. – kiamlaluno Jul 14 '15 at 16:29
  • Hello, i've just updated my question please check it – Mitch Jul 15 '15 at 15:23
  • I have updated my answer. – kiamlaluno Jul 17 '15 at 20:12
1

The Database abstraction layer would help make this far more simple. For example:

$results = db_select('table_name', 't')
    ->fields('t')
    ->condition('t.column_name', $form_state['storage'])
    ->execute();
  • 1
    The OP explicitly stated they don't want to make a static query dynamic, which means they don't want to use db_select(). – kiamlaluno Jul 14 '15 at 16:32
  • And just to note for prosperity sake. It is well documented that db_query() executes quicker than the DB abstraction layer calls such as db_select(). – Brady Jul 17 '15 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.