db_query and db_select

Question

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 ?

Answer 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

Answer 2

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();