Văn Lộc Văn Lộc - 2 months ago 7
PHP Question

How to remove duplicate rows have the same id in Join Query?

I using Kohana - a framework base on PHP to make a search function to search all name in two table, and it doesn't duplicate.

Two table have struct like this:

content - content_revision





id - id
title - content_id
- title


I tried with my code like:

$contents = Jelly::select("content")
->join('content_revision')
->on('content.id', '=', 'content_revision.content_id')
->or_where("content.title", "LIKE", "%" . $value . "%")
->or_where("content_revision.title", "LIKE", "%" . $value . "%");


It shows result not enough and id of all rows in a result is same.

Because my data in table
content_revision
have many rows duplicate (content_id is same after duplicates).

I tried to use
left join
, but it also shows the same result.

I think using
distinct
keyword to get only rows and it is unique.

So, I research on Internet but don't have any document to custom.

I found this link:

http://jelly.jonathan-geiger.com/docs/api/Jelly_Builder_Core#distinct

But the server not found.

Have any method to resolve my problem?

Thanks.

Answer

You should try group_by instead.

$contents = Jelly::select("content")
              ->join('content_revision')
              ->on('content.id', '=', 'content_revision.content_id')
              ->or_where("content.title", "LIKE", "%" . $value . "%")
              ->or_where("content_revision.title", "LIKE", "%" . $value . "%")
              ->group_by("content.id");