4

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.

Ave
  • 4,338
  • 4
  • 40
  • 67

2 Answers2

2

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");
Rax Weber
  • 3,730
  • 19
  • 30
  • Working with me. But I also want to get data of table `content`. So, `join` seem not get all data from two table. I read at here: http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join . So, I tried to using `FULL OUTER JOIN` but seem it not working. I working in MySQL database. Thanks. – Ave Sep 29 '16 at 06:20
1

Try this one, as it will give you only last revision to compare for title

$contents = Jelly::select("content")
              ->join('(SELECT CR.content_id,CR.title, MAX(CR.id) AS maxid
            FROM content_revision CR GROUP BY CR.content_id) as LatestRevision')
              ->on('content.id', '=', 'LatestRevision.content_id')
              ->or_where("content.title", "LIKE", "%" . $value . "%")
              ->or_where("LatestRevision.title", "LIKE", "%" . $value . "%")
              ->group_by("content.id");