Member Avatar for davehere

hello, I need to display contents with their tags, and in same page all the tags of the page contents
the problem is having a limit on contents doesn't work if join tags on them (the number of tags change the number of select contents)


THE TABLES
==========

I have tables :

> contents
> tags
> link_contents_tags (id_tag, id_content)

I need to select contents with tags so for each content I display its tags
I have a pager so I limit the select of contents

the problem is if I make a
> select contents JOIN tags LIMIT xcontents
it won't work because of the y number of tags on each content
and to limit the number of tags is not a solution because it's unkwnown number (it can have no tag or 10)


EXAMPLE
=======

so limit of 3 contents must display :

CONTENTS:
> [B]content_1 [/B]
>> tag1_content1
>> tag2_content1
>> tag3_content1

> [B]content_2[/B] 
>> (no tags)

> [B]content_3 [/B]
>> tag1_content3

TAGS:
tag1_content1
tag2_content1
tag3_content1
tag1_content3

but if I set
LIMIT 0, 3
on select contents join tags it'll not display 3 contents but just the first because of the join on x tags


A (not beautiful) SOLUTION
==========================

a solution is to

> select contents with search criterias
> read ids of found contents
> select tags JOIN links WHERE id_content IN (list ids found contents) ORDER BY id_content
> order tags in an array by content_id : arTags[content_id]=array rows tags
> display all contents, for each content display tags from tags array if exist 
> display next all contents from this page

it'll work with contents pager and unknown tags number by content
but it seems a little rude :)
two requests (can be ok) but a few loops on contents that I don't like

have any idea to simplify it?
I don't like to make ugly code :)

think's in advance

Edited by davehere because: n/a
  • 3 Contributors
  • 5 Replies
  • 204 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by davehere
Member Avatar for smantscheff

You can limit the paged entries in a SELECT DISTINCT clause like that:

drop table if exists a;
drop table if exists b;
drop table if exists ab;
create table a (id integer);
create table b (id integer);
create table ab (a_id integer, b_id integer);
insert into a values (1),(2),(3),(4),(5);
insert into b values (1),(2),(3),(4),(5);
insert into ab values (1,1),(1,2),(1,3),(2,1),(2,2);

/* page 1 */
select a.*, b.*
from a 
inner join (
  select distinct a_id from ab order by a_id limit 0,1
) x
on a.id=x.a_id
inner join ab
on a.id=ab.a_id
inner join b
on ab.b_id=b.id
;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+

/* page 2 */
select a.*, b.*
from a 
inner join (
  select distinct a_id from ab order by a_id limit 1,1
) x
on a.id=x.a_id
inner join ab
on a.id=ab.a_id
inner join b
on ab.b_id=b.id
;
+------+------+
| id   | id   |
+------+------+
|    2 |    1 |
|    2 |    2 |
+------+------+
commented: take time to write beautiful and complate example +0
Member Avatar for pritaeas

If the tags are for displaying only, wouldn't a GROUP_CONCAT be useful ?

commented: found just beautiful and perfect solution who saves me a lot of code +0
Member Avatar for davehere

hey think for your quick - and good - responses ! :)

Smantscheff big think to have taken time to write complate example, it learns me better use to select distinct

and, wahou think pritaeas it's so beautiful! :)

so I'll use

GROUP_CONCAT(tag.name) as tags_names, GROUP_CONCAT(tag.type) as tags_types
...
GROUP BY content.id

and after in php explode on tags_names and tags_types to have them
there'll be a little time price in group by and working array but I think I won't find more simple

think you two you made my week!

Member Avatar for davehere

I have also to get count contents of tags (a column in tags with update +1 when a new content on it) and order on count, but these works :

GROUP_CONCAT(tag.name ORDER BY tag.count_contents DESC) as tags_names, 
GROUP_CONCAT(tag.typename  ORDER BY tag.count_contents DESC) as tags_types, 
GROUP_CONCAT(CAST(tag.count_contents AS CHAR) ORDER BY tag.count_contents DESC) as tags_counts, 
...
JOIN tags
...
GROUP BY node.id

result :

content   | tagname          |  tagcount | ...
the clash | music rock punk  | 20 14 8

with for each content get its tags order by count of all contents on tags
(here there is a lot of contents on music, some on rock and a few on punk)


is it right for performing, or is it bad practice to have these 3 group_concat?

I'll have work on server to manage array of tags by contents but I think I can't avoid it (because in all cases I'll have to extract tags array from each content row)

think! :)

Edited by davehere because: n/a
Member Avatar for davehere

well I have found it I think it's ok with only one group_concat

GROUP_CONCAT(CONCAT(tag.typename,','), CONCAT(tag.name,','), CAST(tag.count_contents AS CHAR) ORDER BY tag.count_contents DESC SEPARATOR ';') as tags,

I think it's better performing with just one group_concat

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.