1

I'm using laravel-4. My model scheme created as you would expect.

Here's my database scheme:

CREATE TABLE tags
    (`id` int, `name` varchar(4));

INSERT INTO tags
    (`id`, `name`)
VALUES
    (1, 'tag1'),
    (2, 'tag2'),
    (3, 'tag3'),
    (4, 'tag4');

CREATE TABLE entries
    (`id` int, `body` varchar(5));

INSERT INTO entries
    (`id`, `body`)
VALUES
    (1, 'post1'),
    (2, 'post2'),
    (3, 'post3'),
    (4, 'post4');

CREATE TABLE entry_tag
    (`entry_id` int, `tag_id` int);

INSERT INTO entry_tag
    (`entry_id`, `tag_id`)
VALUES
    (1, 1),
    (2, 1),
    (2, 2),
    (2, 3),
    (3, 2);

And this is my SQL query

SELECT *
FROM entries a
INNER JOIN (
    SELECT p.id
    FROM entry_tag tp
    INNER JOIN entries p ON tp.entry_id = p.id
    INNER JOIN tags t ON tp.tag_id = t.id
    WHERE t.NAME IN (
            'tag1'
            ,'tag2'
            ,'tag3'
            )
    GROUP BY p.id
    HAVING COUNT(DISTINCT t.id) = 3 -- << should the number of tags used in WHERE clause
    ) q ON a.id = q.id

Demo: http://sqlfiddle.com/#!2/cdca7/3

Question

I'll pass tags array to this query. But there's a few thing about SQL expression, I can't use it even raw PDO query without regGUID. You can ask me what did you tried but I have no idea how to do this clean Laravel syntax and Eloquent? (of course I googled)

Community
  • 1
  • 1
alayli
  • 325
  • 1
  • 6
  • 18
  • query builder and eloquent are useful only for simple queries. for complex ones, you need to go with the plain old sql.... use the tools at your convenience. – itachi Jan 06 '14 at 04:34
  • Thanks, so there way qucikly set up PDO db connection to bind parameters in Laravel. (Also if you write as answer, I can mark it) – alayli Jan 06 '14 at 13:35

1 Answers1

0

Here you go. To note, Laravel 4.1 (actually Eloquent) is needed to query relationship.

// Eloquent magic here :)
return Entry::has('tags', '=', 3)->get();

// ... and minimal setup for models

class Entry extends Eloquent {

    public function tags()
    {
        return $this->belongsToMany('Tag');
    }
}

class Tag extends Eloquent {

}
Andreyco
  • 22,476
  • 5
  • 61
  • 65