OctoberCMS db query and json

0
votes

I have db table categories with columns

id name types active
  • ID - INT, auto increment
  • NAME - VARCHAR
  • TYPES - JSONABLE
  • ACTIVE - INT

DB record example

1 | Test Category | ["1","2"] | 1
2 | Another One | ["1","2","3"] | 1

Now i want to create a query to get category is active = 1 and type is in array (from types column).

For example my type is 3 and query should look if 3 exist in ["1","2","3"]

$categories = Db::table('categories')
    ->where('types', '3') <- ??
    ->where('active', 1)
    ->get();

How can i do it?

1
phpmysqljsonoctobercms
You are looking for FIND_IN_SET(). Don't know OctoberCMS, but my guess is it will be similar to stackoverflow.com/questions/35594450/… – Sean

1 Answers

0
votes

And once again i am answering my own questions. After quite few atempts to solve it in component i have simply made it work in partial using Twig function.

So the answer is:

Query

$categories = Category::where('active', 1)->get();

Function in partial

{% for category in categories %}
    {% if type in category.types %}
        {{ category.name }}
    {% endif %}
{% endfor %}

Now it perfectly shows only categories where type (in this case 3) is in jsonable column (as per my explanation in question).