• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

PHP PHP Query Issue

Fait

New Coder
Hello,
I have created a status system similar to Twitter or Facebook it works except it does not load the posts of the logged-in user onto the feeds,
Unless I make the user follow themselves which then takes up unnecessary database space.

Code:
 $GetLoadCount = $db->fetchRow("SELECT  xf_social_feeds.*,  xf_user_follow.*
FROM xf_user_follow
JOIN xf_social_feeds
  ON xf_user_follow.follow_user_id = xf_social_feeds.user_id_feeds
WHERE xf_user_follow.user_id = '$uid'  ORDER by id DESC LIMIT $DefaultLoadLimit OFFSET 4");

I can normally solve 99% of web development issues but this one I've been stuck on for weeks,
How can I modify this so it loads the people they follow and their own feeds?

Thank you!
Fait
 

Fait

New Coder
This is the social feeds table, custom-made by me, The following table is the same as Xenforo since my site is run by that framework.
it just checks who follows each other then returns posts based on that.
FeedsStructure.JPG
 

Ghost

Active Coder
I leave it, not the best practice but it works and I'm only 1 developer.
You can use a UNION SELECT if you want to combine the results of two separate selects.

You could also just select straight from the feeds and do a subquery (WHERE account id IN (select myuserid, usersifollow...)

You could do a LEFT JOIN to get all results, but then in your WHERE you could put that the user id is not null OR (follow user id is null AND post author is logged in user), but I'm not a fan of this solution personally.

---------
I wish I could share some code of my own, but I can't legally release it. My company just finished building a proprietary 100% custom social network. We have some feed queries that are super long:
- Show all my posts in feed
- Show posts of my friends in feed
- Show posts in groups I belong to in feed
- Show posts in events that I am invited to / did not decline yet
- Show posts that my friends have shared from others
- Show posts on blog articles posted by me / groups / friends
- Only show posts if the author has set their post to public, friends only (and you're friends), friends of friends (and you have mutual friends), friends of tagged, etc
- Do not show posts from a user that you have blocked
- Do not show posts from a user that has blocked you
..... + more!

Then with all of those queries for the feed, we then have to make sure they show up in a certain order. We have crons that will update scores for the posts based on likes, comments, and the time since the post was made (very similar to the reddit algorithm to be honest).

We have multiple versions of the following code depending on what must be loaded in, and we're always looking for new ways to lower the database resources needed. One thing we do right is caching & making sure to give as much work to the client (via JavaScript) as possible (without slowing their browser) to prevent server-side work.

Example of 1 query - This one below is the first edition and is being depreciated in 2.0 - v1.0 used a lot of union selects to combine data.

newsfeed-joins.png
 

Fait

New Coder
You can use a UNION SELECT if you want to combine the results of two separate selects.

You could also just select straight from the feeds and do a subquery (WHERE account id IN (select myuserid, usersifollow...)

You could do a LEFT JOIN to get all results, but then in your WHERE you could put that the user id is not null OR (follow user id is null AND post author is logged in user), but I'm not a fan of this solution personally.

---------
I wish I could share some code of my own, but I can't legally release it. My company just finished building a proprietary 100% custom social network. We have some feed queries that are super long:
- Show all my posts in feed
- Show posts of my friends in feed
- Show posts in groups I belong to in feed
- Show posts in events that I am invited to / did not decline yet
- Show posts that my friends have shared from others
- Show posts on blog articles posted by me / groups / friends
- Only show posts if the author has set their post to public, friends only (and you're friends), friends of friends (and you have mutual friends), friends of tagged, etc
- Do not show posts from a user that you have blocked
- Do not show posts from a user that has blocked you
..... + more!

Then with all of those queries for the feed, we then have to make sure they show up in a certain order. We have crons that will update scores for the posts based on likes, comments, and the time since the post was made (very similar to the reddit algorithm to be honest).

We have multiple versions of the following code depending on what must be loaded in, and we're always looking for new ways to lower the database resources needed. One thing we do right is caching & making sure to give as much work to the client (via JavaScript) as possible (without slowing their browser) to prevent server-side work.

Example of 1 query - This one below is the first edition and is being depreciated in 2.0 - v1.0 used a lot of union selects to combine data.

View attachment 1078
thank you for your reply! :)
I definitely look in to everything you've mentioned and find the best solution.
 

Top