Laravel: retrieve count of value in JSON array

Question

Iv currently taken over a project where the developer has stored has many relationships in json array columns in certain tables.

product table

----------------------------
id | product | colour
----------------------------
 1   iPhone    ["8","4","1"]
 2   iPad      ["8","1"]
 3   Macbook   ["8"]

This is an example of the relationships stored between a product and product colours available.

I need to be able to get the count of products, related to a certian colour. So in this example... colour: 8 would return 3 products

I am use to Eloquent relationships utilising $product->colours() but unfortunately in this instance i can't do that, and i am unable to change the current db structure.

How would i be able to get the amount of products per certain colour using eloquent where or whereIn clauses?

What i have tried so far...

$count = $products->where('colour', '[$colour->id]')->count();
----
$count = $products->whereIn('colour', $colour->id)->count();

Any help would be greatly appreciated. Thanks in advance.

I hope you fired the "developer" that did such a mess of "relationship" — Amarnasan
– Amarnasan, Commented Nov 14, 2017 at 9:36
$count = $products->where('colour', $colour->id)->count(); ? — JérémyCasper
– JérémyCasper, Commented Nov 14, 2017 at 9:37
@Amarnasan when i saw the database i almost had a heart attack, no pivot tables for hasMany relationships. — user4258584
– user4258584, Commented Nov 14, 2017 at 9:40
@JérémyCasper i have already tried that, returns an empty array — user4258584
– user4258584, Commented Nov 14, 2017 at 9:40
I think this developer missed the whole point of eloquent relationships — ADoorMarkedPirate
– ADoorMarkedPirate, Commented Nov 14, 2017 at 9:40

Amarnasan · Accepted Answer · 2017-11-14 09:49:31Z

3

This is ugly, but I guess it works:

\App\Product::where('colour','like','%"' .$colour->id '"%')->distinct()->count()

edited Nov 14, 2017 at 9:49

answered Nov 14, 2017 at 9:42

Amarnasan

15.7k7 gold badges35 silver badges38 bronze badges

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

4 Comments

Tpojka Over a year ago

That would return 8, 18, 228,... for query='8'. Normalization with colors table would be a must imho. Eventually json field instead of string.

Amarnasan Over a year ago

@Tpojka No, it wouldn't.... because of the double quotes surrounding the color code. Read before ranting.

Tpojka Over a year ago

What would return %8%?

Amarnasan Over a year ago

@Tpojka What would return %"8"% ? Still don't get it?

Collectives™ on Stack Overflow

Laravel: retrieve count of value in JSON array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related