Search on many-many relations (e.g. tags)

Questions
13

For some reason I saw your code similar to the other one… but it is not.

The filter call does not work because there is a duplicate array key. Surprisingly PHP does not error out but it silently runs your code applying only the second condition ('Categories.Title' => 'B'). Using two filter calls did not solve the issue either: it returns an empty set :cold_sweat:

Here is my test code:

use SilverStripe\ORM\DataObject;

class Category extends DataObject
{
    private static $db = [
        'Title' => 'Varchar',
    ];
    private static $many_many = [
        'Bookmarks' => Bookmark::class,
    ];

    public function requireDefaultRecords()
    {
        parent::requireDefaultRecords();
        foreach (['A','B','C','D'] as $id => $title) {
            $row = Category::create();
            $row->ID = $id + 1;
            $row->Title = $title;
            $row->write();
        }
    }
}

class Bookmark extends DataObject
{
    private static $db = [
        'Title' => 'Varchar',
    ];
    private static $belongs_many_many = [
        'Categories' => Category::class,
    ];

    public function requireDefaultRecords()
    {
        parent::requireDefaultRecords();
        foreach (['A','AB','ABC','ABCD','BCD','CD','D'] as $title) {
            $row = Bookmark::create();
            $row->Title = $title;
            $row->write();
            foreach (str_split($title) as $ch) {
                $row->Categories()->add(ord($ch) - ord('A') + 1);
            }
        }
    }
}

And here are my results.

// Ok: [ A, AB, ABC, ABCD ]
Bookmark::get()->filter('Categories.Title', 'A')->column('Title');

// Ok: [ AB, ABC, ABCD, BCD ]
Bookmark::get()->filter('Categories.Title', 'B')->column('Title');

// Error: []
Bookmark::get()->filter('Categories.Title', 'A')->filter('Categories.Title', 'B')->column('Title');

// Error: []
Bookmark::get()->filter([ 'Categories.Title' => 'A', 'Categories.ID' => 2 ])->column('Title');

// Ok but with duplicates: [ A, AB, ABC, ABCD, AB, ABC, ABCD, BCD ]
Bookmark::get()->filter('Categories.Title', ['A', 'B'])->column('Title');

// Ok: [ A, AB, ABC, ABCD, BCD ]
Bookmark::get()->filter('Categories.Title', ['A', 'B'])->columnUnique('Title');

// Error: []
Bookmark::get()->filter('Categories.Title', ['A', 'B'])->filter('Categories.Title', ['C', 'D'])->column('Title');

There is something weird with many-many queries or I fail to see something big.