Category not showing in category list

SilencerNL · Post by **SilencerNL** » 23 Dec 2007, 11:25

Hello Jan,

Great idea enabling error reporting... I havent thought of that!

It looks like there is an error in the query which returns all categories. The following query is submitted to the database:

SELECT cc. * , a.catid, COUNT( a.id ) AS numlinks, f.filename AS filename,
CASE WHEN CHAR_LENGTH( cc.alias )
THEN CONCAT_WS( ':', cc.id, cc.alias )
ELSE cc.id
END AS slug
FROM jos_categories AS cc
LEFT JOIN jos_phocagallery AS a ON a.catid = cc.id
LEFT JOIN jos_phocagallery AS f ON f.catid = cc.id
WHERE a.published =1
AND f.filename = (
SELECT filename
FROM jos_phocagallery AS ff
WHERE ff.catid = cc.id
AND ff.published =1
ORDER BY ff.ordering ASC
LIMIT 1 )
AND cc.section = 'com_phocagallery'
AND cc.published =1
AND cc.access <=0
GROUP BY cc.id
ORDER BY cc.ordering
LIMIT 0 , 30

If I submit this query manualy to the database, it returns 0 rows. (I use MySQL 5 btw)

It should return 2 rows, since I have 2 categories (checked in the database, they are there )

SilencerNL · Post by **SilencerNL** » 23 Dec 2007, 11:36

this is very interesting...

I think the error has to do with your sub-query in the query posted above, the SELECT filename part.

If I have only 1 image in a category, it shows correctly, but If I have 2 or more images, it doesnt show the category anymore.

I'll do some more debugging... keep you posted.

SilencerNL · Post by **SilencerNL** » 23 Dec 2007, 11:51

Hey Jan,

me again... I solved the problem. I just removed the subquery from the query above, and everything works ok.

I commented line 63 in joomla/components/com_phocagallery/models/categories.php:

$query = 'SELECT cc.*, a.catid, COUNT(a.id) AS numlinks, f.filename as filename,
'
. ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(\':\', cc.id, cc.alias) ELSE c
c.id END as slug'
. ' FROM #__categories AS cc'
. ' LEFT JOIN #__phocagallery AS a ON a.catid = cc.id'// for COUNT e.g.(3)
. ' LEFT JOIN #__phocagallery AS f ON f.catid = cc.id'// for IMAGES
. ' WHERE a.published = 1'
//. ' AND f.filename =(SELECT filename FROM #__phocagallery AS ff WHERE
ff.cati
d
= cc.id AND ff.published = 1 ORDER BY ff.ordering ASC LIMIT 1)'
. ' AND cc.section = \'com_phocagallery\''
. ' AND cc.published = 1'
. ' AND cc.access <= '.(int) $gid
. ' GROUP BY cc.id'
. ' ORDER BY cc.ordering';

Post by **Jan** » 23 Dec 2007, 14:47

If the line is there, you get the image (next to category name) which is ordered as first. It means, user can select, which image will be displayed next to category name in categories view (just order the image as first)...

If the line is not there, user cannot select the image next to category name, it will be displayed image with the lowes ID...

But, in the next Phoca Gallery version, I will do a new categories behaviour... The SQL will be changed...

Now you can use this SQL, try it please:
$query = 'SELECT cc.*, a.catid, COUNT(a.id) AS numlinks,'
. ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(\':\', cc.id, cc.alias) ELSE c
c.id END as slug'
. ' FROM #__categories AS cc'
. ' LEFT JOIN #__phocagallery AS a ON a.catid = cc.id'
. ' WHERE a.published = 1'
. ' AND cc.section = \'com_phocagallery\''
. ' AND cc.published = 1'
. ' AND cc.access <= '.(int) $gid
. ' GROUP BY cc.id'
. ' ORDER BY cc.ordering';

Jan

SilencerNL · Post by **SilencerNL** » 23 Dec 2007, 15:54

Hey Jan,

Your sql query also works, but then I dont see any images beside the category.

Post by **Jan** » 23 Dec 2007, 16:17

Yes, you right, I forgot 'a.filename':

$query = 'SELECT cc.*, a.catid, COUNT(a.id) AS numlinks, a.filename as filename,
'
. ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(\':\', cc.id, cc.alias) ELSE cc.i
d END as slug'
. ' FROM #__categories AS cc'
. ' LEFT JOIN #__phocagallery AS a ON a.catid = cc.id'
. ' WHERE a.published = 1'
. ' AND cc.section = \'com_phocagallery\''
. ' AND cc.published = 1'
. ' AND cc.access <= '.(int) $gid
. ' GROUP BY cc.id'
. ' ORDER BY cc.ordering';

SilencerNL · Post by **SilencerNL** » 23 Dec 2007, 16:20

perfect, works like a charm!

Thanks for your help Jan!

Post by **Jan** » 23 Dec 2007, 16:28

This is an original SQL query, which Phoca Gallery used... Then I created more complicated SQL because of user can select the image, he wants to display in categories view... It works for me on localhost (WinXP, Suse Linux) and on server (FreeBSD).... But I will change the categories (database table and behaviour, there will be not used com_category) in next version... so there will be other SQL query...

Jan