Laravel - Filter collection by checking if value exists in array in database

Question

I want to return a collection of posts, based on if a single value exists in an array stored in the table.

In my posts table, I have regular column names like 'title', 'body', and 'slug'. I am managing multiple sites using the same table, so I have an additional column name of 'site_ids'. When a user creates a post, they select what sites they want it published on and it stores the site id's as an array (eg. [1,2])

-----------------------------------------------
| Title         | Site_ids     | Slug         |
-----------------------------------------------
| Test Title    |[1,2,3]       |test-title    |
-----------------------------------------------
| Another Title |[1,6]         |another-title |
-----------------------------------------------

Now I am creating an API to return all posts with the site_id of 1. How can this be achieved? Here is my current code in my PostController

API ROUTE

Route::get('/{site_id}/posts','PostController@index')->name('Show Posts');

Code:

  public function index($site_id)
    {
        // Return all posts by id
        $posts = Post::whereIn('sites', $site_id)->get();
        return new PostCollection($posts);
    }

I am getting this error message

Invalid argument supplied for foreach()

What am I doing wrong here?

What's the foreach() in this context? Is PostCollection a wrapper for a view()? — Tim Lewis
– Tim Lewis, Commented Sep 24, 2018 at 19:11
@TimLewis Its a Laravel Post Collection Resource laravel.com/docs/5.6/eloquent-resources — gbland777
– gbland777, Commented Sep 24, 2018 at 19:38
Ah yes, that's my bad; didn't see API (bolded and everything); must be Monday. That aside, I thought whereIn() converted the second param to an array if it wasn't one, but the answers below seem to be correct to handle that. Does that query work though? I wouldn't think that storing literal [1, 2] would work with whereIn()... — Tim Lewis
– Tim Lewis, Commented Sep 24, 2018 at 19:43
Lol it is a Monday! The query is working now after I wrapped it in brackets. However its returning 0 items. Its like its not going through the array in my table? — gbland777
– gbland777, Commented Sep 24, 2018 at 19:48
Or, to handle this currently, you could use a ->where("sites", "LIKE", "%".$site_id."%"); to handle a wildcard text search (loosely matching [1, 2] to 1, etc), but that's inefficient and not the best approach. — Tim Lewis
– Tim Lewis, Commented Sep 24, 2018 at 19:56

markantonay · Accepted Answer · 2018-09-25 05:44:33Z

1

you can try whereRaw('JSON_CONTAINS(meta, "1")') something like that. check for the command in mysql. and also if your running in 5.7 of laravel you might want to check this

answered Sep 25, 2018 at 5:44

markantonay

1425 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

whmkr · Accepted Answer · 2018-09-24 20:43:05Z

0

I know this is likely to get a downvote but honestly this is what you're doing wrong:

"I am wanting to return a collection of posts, based on if a single value exists in an array stored in the table."

You should set up a many to many relation between Posts and Sites with a pivot table and then you could just do something like this:

Site::find($id)->Posts;

https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

answered Sep 24, 2018 at 20:43

whmkr

3,0952 gold badges23 silver badges32 bronze badges

Comments

Elisha Senoo · Accepted Answer · 2018-09-25 19:51:18Z

0

The second parameter for whereIn must be an array. You should do this:

public function index($site_id)
{
    // Return all posts by id
    $posts = Post::whereRaw('JSON_CONTAINS(sites, \'{$site_id}\')')->get();
    return new PostCollection($posts);
}

edited Sep 25, 2018 at 19:51

answered Sep 24, 2018 at 19:15

Elisha Senoo

3,6092 gold badges24 silver badges30 bronze badges

2 Comments

gbland777 Over a year ago

So I wrapped the value with the brackets and now I am not receiving the error message. However it is returning 0 items. Any ideas?

Elisha Senoo Over a year ago

I have updated it, this should work. I have not tested though. But you will get the idea.

Collectives™ on Stack Overflow

Laravel - Filter collection by checking if value exists in array in database

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related