Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
query on multi-categories
  • efxefx
    Hey,

    I'd would like manage post for multi-categories,
    My post has already  main category :
    $post->category = $ID;
    For store others categories I though to its eav table :
    $post->categories = "id1,id2,id3"

    But after, what is the best to request posts on a category on eav ?
    is the query() where allow Regex ?
    Model_Post::query()->where('category', $id)->or_where('categories', '*$id*'); ?

    or maybe its a bad design to do it in this way ?

    any recommendation ?

    thanks
  • efxefx
    I found this : https://forums.mysql.com/read.php?10,392332,392950#msg-392950
    not sure if fuel work with REGEXP in ORM, but I can work with LIKE
  • HarroHarro
    You can do that using DB::expr(), but the question is if this is the correct design. 

    It is probably better, from a design point of view, to change the relation post-category to a many-many, which is clearly is.
  • efxefx
    Ah ok thanks for the tip and your viewpoint, its always welcome.
    Take care
  • efxefx
    I have now a many to many relation with categories_posts,
    How I query with ORM posts with any category_id ? 
    would like it :
    Model_Post::query()->related('categories')->where('category_id',5)->get(); ?

    the setup in DB for post and categories are :

    post :
    protected static $_many_many = array(
            'categories' => array(
                'key_from'          => 'id',
                'key_through_from'  => 'post_id',         
                'table_through'     => 'categories_posts',
                'key_through_to'    => 'category_id',
                'model_to'          => 'Model_Category',
                'key_to'            => 'id',
                'cascade_save'      => false,
                'cascade_delete'    => false,
            )
        );

    category :
    protected static $_many_many = array(
            'categories' => array(
                'key_from'          => 'id',
                'key_through_from'  => 'category_id',
                'table_through'     => 'categories_posts',
                'key_through_to'    => 'post_id',
                'model_to'          => 'Model_Post',
                'key_to'            => 'id',
                'cascade_save'      => true,
                'cascade_delete'    => false,
            )
        );

    thanks
  • efxefx
    it works only with this syntax :

    // it request all posts with category's id=5
    \Model_Post::query()->related('categories', array('where' => array(array('id', 5))))->get();

    // it test post id=1 IF it's in category id=6
    \Model_Post::query()->where('id',1)->related('categories', array('where' => array(array('id', 6))))->get();

    is it correct way ?
  • efxefx
    I got it now Guys, same with chain and dox syntax :
    \Model_Post::query()->related('categories')->where('categories.id', 5)->get();
  • HarroHarro
    Accepted Answer
    Thanks for figuring it out yourself. ;-) I was busy elsewhere...
  • efxefx
    hehe no problem ;) 
  • efxefx
    I have a small trick :(
    I'd would like break all relations from a Post then rebuild a new one : https://bin.fuelphp.com/snippet/view/O2

    I have an SQLSTATE[23000] error,
    is the many_many categories_posts  table should have an id ?
    \DBUtil::create_table('categories_posts', array(
    'category_id'  => array('constraint' => 11, 'type' => 'int'),
    'post_id'      => array('constraint' => 11, 'type' => 'int'),
    ), array('category_id', 'post_id'));
  • efxefx
    I have this error, but it write the right data in Mysql.

    It seem I can't break relation then after connect to a new one. I dont understand
  • HarroHarro
    Accepted Answer
    SQLSTATE[23000] suggests you have defined foreign key constraints?

    Do you have information on what code exactly generates this error? And the complete error text?
  • efxefx
    here the code running :

    here the logs with the error :

    it run fine the code but I have this SQL error warning.
    Iam on local with Fuelphp 1.8.0.3 / MAMP PHP 7.1.8 / MySQL 5.6.35
  • HarroHarro
    Accepted Answer
    Ok, clear. So it tries to insert a new junction record while one already exists. This suggests that breaking the relation fails, which would mean a major bug.

    Can you also post your models and the table creation code, so I can replay your situation here and debug it?
  • efxefx
    is it enough files for you:
    thanks Harro
  • HarroHarro
    Accepted Answer
    Thanks. I'll have a look later today,
  • HarroHarro
    Accepted Answer
    Sorry for the late response, had a firewall panic yesterday. ;-)

    Tried your code here on 1.9/dev, and no problem. It removes the junction record on the unset, and re-inserts it again on the assignment. See https://bin.fuelphp.com/snippet/view/OA

    So perhaps you've bumped into an issue that has already been adressed in 1.9/dev?

    I really need to find time for 1.8.1.
  • efxefx
    hehe no problem Harro, cool you had not issue on it.
    Ok I'll move on 1.9/dev this weekend and test it.
    see you
    thanks
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion