XF 2.2 Is there a blob count?

[Robert9]

What do you to know hoe many reactions a post has?
Fetch the data and count?

Is there something for mysql to say:

select *post where count(blobfield_name) > 14

 

[digitalpoint]

SELECT COUNT(*) FROM xf_reaction_content WHERE content_type = "post" AND content_id = {POST ID HERE}

Could also do it with a finder on the entity, but raw SQL would be that.

 

[Robert9]

Thank you, that's the solution.

But I can't do this without the information from the second table?
I can't query with the count of the field itself? There is no magic number saved in MySQL like:
In this blob_field are 99 red luftballons!

 

[digitalpoint]

If you are trying to get a count for all posts, you could do it with a JOIN. The xf_post table does have a reaction_score, but that would be the closest thing to an actual counter. The issue with using it, is that's score, not a count. If all your reactions are worth 1 point, then the score is also a count. But if you have reactions that are something other than 1 (by default Wow, Sad and Angry are 0 points), then the score isn't the same as a count (and you would need to query for it).

 

[Robert9]

Thank you.
I want to show some posts and work on the criterias or minimum required criterias like:

at least ten people have voted, reacted, the vote_score is higher than x, the reaction_score is higher than y.

I will use the count from the reaction_table; or maybe add a new field reaction_count ... but the best would be.

I guess there is a reason why we save the usernames in a blob in the table itself - to avoid fetching them, right?
But perhaps a count reaction for post x is less work than getting all usernames from reactions for post x.

If there is any smoke around the server, I will add a new field to post? Ok?

 

[digitalpoint]

Well, depending on how exactly you are doing things, you could in fact get a count without an additional query. For example if you already have the post entity (which you most likely in the template), you could just count the entries in the blob like so:

{{ count($post.reaction_users) }}

But again, it depends on what you are trying to do. That will count it if you have the post entity, but if you are trying to sort or search on the reaction count, that's not going to work (need SQL for that). But a simple count output when you already have the post... it works.

 

[Robert9]

Sorting will happen only with direct useable post_fields.

But I want to filter like:

select from post where number of reactions > x and score > y.

So, I can have a crowd controlled result, when there is a crowd (> x reactions)

But for this, I have to count in the reactions table.
Or add a field to post (and save the number there) or have an extra table to save this count.

Maybe I use the votes only; I wanted to offer both.
Also, I could do a vote with every reaction, then I have a count.