Number of items per type

davidote
Topic Author
Offline
Premium Member

10 years 1 week ago #55359 by davidote

Number of items per type was created by davidote

I'm trying to get the number of items per category using SQL, but I can not show me the correct amount.

Code:

SELECT distinct fcsjb_flexicontent_types.name AS Type, COUNT( fcsjb_content.id ) AS Num
FROM fcsjb_categories
INNER JOIN fcsjb_flexicontent_cats_item_relations ON fcsjb_flexicontent_cats_item_relations.catid = fcsjb_categories.id
INNER JOIN fcsjb_flexicontent_fields_item_relations ON fcsjb_flexicontent_fields_item_relations.item_id = fcsjb_flexicontent_cats_item_relations.itemid
INNER JOIN fcsjb_flexicontent_fields_type_relations ON fcsjb_flexicontent_fields_type_relations.field_id = fcsjb_flexicontent_fields_item_relations.field_id
INNER JOIN fcsjb_flexicontent_types ON fcsjb_flexicontent_types.id = fcsjb_flexicontent_fields_type_relations.type_id
INNER JOIN fcsjb_content ON fcsjb_content.id = fcsjb_flexicontent_cats_item_relations.itemid
WHERE fcsjb_content.state= 1 group by  fcsjb_flexicontent_types.name order by fcsjb_flexicontent_types.name ASC limit 20

Any help is welcome.

Regards

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

ggppdk
Offline
Administrator

10 years 1 week ago #55363 by ggppdk

Replied by ggppdk on topic Number of items per type

Hello

your forum thread title suggest 1 thing

Number of items per type

your forum thread description suggests another thing

number of items per category

and your query suggest something else, and also it does not seem to calculate something meaningful because it joins with the categories relations table using the catid instead of itemid

so i don't understand you want to do

-- 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...

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

ggppdk
Offline
Administrator

10 years 1 week ago - 10 years 1 week ago #55364 by ggppdk

Replied by ggppdk on topic Number of items per type

Code:

$db->setQuery('
	SELECT t.type_id AS type_id, COUNT( i.id ) AS Num
	FROM PRX_flexicontent_item_tmp AS i
	GROUP i.type_id
');
$itemsPerType = $db->loadObjectList('type_id');


$db->setQuery('
	SELECT t.type_id AS type_id, rel.catid as catid COUNT( i.id ) AS Num
	FROM PRX_flexicontent_item_tmp AS i
	INNER JOIN PRX_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id
	GROUP i.type_id, rel.catid
');
$itemsPerCat_typeGrouped = $db->loadObjectList('catid');

// Then if you need type name or category name
$db->setQuery('SELECT * FROM PRX_flexicontent_types');
$types = $db->loadObjectList('id');

$db->setQuery('SELECT * FROM PRX_categories');
$cats = $db->loadObjectList('id');

Last edit: 10 years 1 week ago by ggppdk.

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

davidote
Topic Author
Offline
Premium Member

10 years 1 week ago #55378 by davidote

Replied by davidote on topic Number of items per type

With this SQL, I get the number of items listed in each category.

Code:

SELECT distinct name, count(fcsjb_content.id) as Num from fcsjb_flexicontent_items_tmp inner join fcsjb_content on fcsjb_content.id = fcsjb_flexicontent_items_tmp.id inner join fcsjb_flexicontent_types on fcsjb_flexicontent_types.id = fcsjb_flexicontent_items_tmp.type_id where fcsjb_content.state = 1 group by type_id order by fcsjb_flexicontent_types.name ASC

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