MySQL Join Question

[eluser]Zac G.[/eluser]
Hi CodeIgniters,

I have a pretty simple question about how to query for something.

I have the following tables:
- books: stores title, link to book's website, and id of author
- author: stores authors name
- category_relations: stores book_id and category_id (books can have multiple categories)
- categories: stores category names

I am trying to write a query that will pull the following information:
- Title of book, link to book (from books table),
- Author of book
- Categories for a book

What I am having trouble with is that some books have multiple categories.

So, if I use the following query:

Code:

$this->db->from('books');
$this->db->join('authors', 'authors.id = books.author_id');                    
$this->db->join('status', 'status.id = books.status_id');                            
$this->db->join('category_relations', 'category_relations.book_id = books.id');
$this->db->join('categories', 'categories.id = category_relations.category_id');                                    
$this->db->group_by('title');
$query = $this->db->get();

A book gets returned as many times as it has a category (book with three categories gets outputted three times).

I vaguely remember having this problem before and being able to solve it with orderyby.

So I added the following to the query:

Code:

$this->db->order_by("title", "desc");

No each title gets printed once, but it only displays one category for the book (instead of all three).

If someone is able to help me out with this I would be very grateful. Sorry for asking more of a SQL question than a CI one

Zac

[eluser]Phil Sturgeon[/eluser]
Sadly you cant have it both ways in MySQL. If its done as one query, you will either get multiple book names or you will get only one category.

If there are a fixed number of possible categories, then you can achieve this with sub queries, but they slightly go against what you are trying to achieve in using active record.

Your best bet, will be to loop through the book results and pull out the categories separately. Sorry!

[eluser]Zac G.[/eluser]
I had a feeling that was what was going to have to happen

What I got confused with was where do I query the categories and then loop through them?

Do I pull the book_id and query for the categories that related to that book and then display them?

Cheers for the help,
Zac

[eluser]mattthehoople[/eluser]
you could throw a DISTINCT in there to eradicate duplicates...

[eluser]sophistry[/eluser]
DISTINCT won't eliminate the "dupes" in this case. This is a normal SQL return set for a join query where you have a relation table: you have some post processing work to do.

If you can fiddle with the query and get your joins by starting at a different table (e.g., start at categories instead of books) that can give you a different result set. Also, you might find that your query speed increases - depending on the structure of your database tables.

[eluser]dmorin[/eluser]
Not sure if you got a final answer or not, but what I would do is run your first query and get all of your books and their authors. Then loop through those results and create a new array containing each book id. Then, run a second query and use:

Code:

$this->db->where_id('category_relations.book_id', $book_ids_array);

This will return a list of all categories for each supplied book. Then loop through the original results, print out a book and within that look do a second look to go through the categories and if the current book id matches the item in the categories loop, write it out also.

That's probably not very clear. Hopefully it helps or you already found a solution.

[eluser]depthcharge[/eluser]
Hi Zac,

If you want the results returned from 1 query, use the GROUP-CONCAT group_by function

I believe this is what you are trying to achieve?

Lee

[eluser]sophistry[/eluser]
huh. that's handy to know about. thanks lee74.

joined results returned "tucked into" one record in a parse-able string.