How can I display the number of articles who have a specific value in two different fields ?

5 years 8 months ago - 5 years 8 months ago #61660 by id-pop
Hi,
Sorry to bother you again ! I want to display the number of items (item_id) in a specific category (catid) with the field value 9 for the field_id 19 AND the value 1 in the field_id 39.

Could you please tell me what is wrong in my query ? Thanks a lot

<!-- Nbre d'élément champ + valeur + catégorie + année -->
<div class="agregation donnees">
<?php
$field_id = 19; // CHAMP DONT ON SOUHAITE LE NOMBRE !!!
$field_value = 9; // VALEUR DU CHAMP !!!
$field_annee = 39; // CHAMP CONDITIONNEL !!!
$field_annee_value = 1; // VALEUR DU CHAMP CONDITIONNEL !!!
$_cid = 34; // CATEGORIE !!!
$db = JFactory::getDBO();
$query = 'SELECT COUNT(*) as total'
.' FROM #__flexicontent_fields_item_relations as val'
.' JOIN #__flexicontent_cats_item_relations as rel ON rel.itemid=val.item_id'
.' WHERE (field_id='.$field_id.' AND value='.$field_value.') AND (val.field_id='.$field_annee.' AND val.value='.$field_annee_value.') AND rel.catid = ('.$_cid.') GROUP BY rel.catid'
;
$db->setQuery($query);
$cat_info = $db->loadObject();
?>
<p><b>Nbre d'élément champ + valeur + catégorie + année : 3 </b></p>
<span class="my_fieldNN_total">
<?php
echo JText::_('MY_OUT_OF_TOTAL_ITEMS_IN_CAT').': ';
echo $cat_info->total;
?>
</span>
<?php
?>
</div>

Please Log in or Create an account to join the conversation.

5 years 8 months ago #61663 by micker

FLEXIcontent is Free but involves a very big effort on our part.
Like the our support? (for a bug-free FC, despite being huge extension) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing reviews. Thanks![/size]

Please Log in or Create an account to join the conversation.

5 years 8 months ago #61668 by id-pop
Avec cette solution, je me retrouve avec un résultat vide. Pourtant si je remplace la ligne:
WHERE (field_id='.$field_id.' AND value='.$field_value.') AND (val.field_id='.$field_annee.' AND val.value='.$field_annee_value.') AND rel.catid = ('.$_cid.') GROUP BY rel.catid'

par

WHERE field_id='.$field_id.' AND value='.$field_value.' AND rel.catid = ('.$_cid.') GROUP BY rel.catid

le $cat_info->total me renvoie bien un résultat

Please Log in or Create an account to join the conversation.

5 years 8 months ago #61699 by id-pop
Bonjour,
Je vais essayer d'expliquer autrement mon souci au cas où tu aurais une piste de solution.
J'ai créé plusieurs catégories dont une catégorie Saint-Ouen (catégorie 34)
J'ai créé plusieurs articles avec des champs spécifiques. Chaque article correspond à une séance.
Le champ 19 est le champ thématique. La valeur 9 est la thématique bâtiment.
Le champ 39 est l'année . La valeur 1 est l'année 2012.
Je veux connaître le nombre de séance qui ont eu lieu en 2012 sur la thématique bâtiment à Saint-Ouen.

Donc à priori la logique à suivre est la suivante :
- 1 : Rechercher la liste des items_id qui ont la "value" 1 quand le "field_id" est 39 et la "cat_id" 34.
- 2 : Comparer la liste d'item_id générée dans l'étape 1 avec la liste totale des item_id et compter ceux qui ont la "value" 9 quand le "field_id" est 19.

Mais je ne vois pas trop comment faire ça... Une sous-requête ?

En fait, ça correspond aux informations affichées dans un module flexifilter (voir visuel)
Attachments:

Please Log in or Create an account to join the conversation.

5 years 8 months ago #61721 by ggppdk
Hello

- if you want to list them then you can create a category menu item
and configure in it the lock fields values parameter

or do you only want to count them ?


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star review. Thanks!

Please Log in or Create an account to join the conversation.

5 years 8 months ago - 5 years 8 months ago #61741 by id-pop
Finally, I have created this code which do what I want. It's not perfect but it works!

<div class="agregation donnees">
<?php
$field_id = 22; // TYPE D'INTERVENTION !!!
$field_value = 1; // Scéances thématiques !!!
$field_annee = 39; // CHAMP ANNEE !!!
$field_annee_value = 4; // 2015-2016 !!!
$_cid = '8,36,9,10,28,29,37,11,38,12,13,30,31,39,14,15,32,33,16,34,35'; // Toutes les CATEGORIES !!!
$db = JFactory::getDBO();
$query = 'SELECT SUM(total)as toto FROM('
.' SELECT COUNT(DISTINCT item_id) as total FROM ('
.' (SELECT * FROM #__flexicontent_fields_item_relations as vale'
.' JOIN #__flexicontent_cats_item_relations as rele ON rele.itemid=vale.item_id'
.' WHERE field_id IN('.$field_id.') AND value IN ('.$field_value.') AND rele.catid IN ('.$_cid.'))'
.' UNION'
.' (SELECT * FROM #__flexicontent_fields_item_relations as val'
.' JOIN #__flexicontent_cats_item_relations as rel ON rel.itemid=val.item_id'
.' WHERE field_id IN ('.$field_annee.') AND value IN ('.$field_annee_value.') AND rel.catid IN ('.$_cid.')))'
.' AS donnemoica'
.' GROUP BY item_id'
.' HAVING COUNT(item_id)>1)'
.' AS toto';

$db->setQuery($query);
$cat_info = $db->loadObject();
?>
<p>Nbre séances thématiques 2015-2016 : OK</p>
<span class="my_fieldNN_total">
<?php echo $cat_info->toto; ?>
</span>
<?php ?>
</div>

Please Log in or Create an account to join the conversation.

Moderators: vistamediajoomlacornerggppdk
© 2018 Flexicontent. All Rights Reserved.
Save
Cookies user prefences
We use cookies to ensure you to get the best experience on our website. If you decline the use of cookies, this website may not function as expected.
Accept all
Decline all
Essential
These cookies are needed to make the website work correctly. You can not disable them.
Display
Accept
Analytics
Tools used to analyze the data to measure the effectiveness of a website and to understand how it works.
Google Analytics
Accept
Decline