Maybe anyone here can help my with following problem:
I have three tables:
posts (userid, time)
otherposts (userid, time) <- this name seems odd but it is only there to show my problem
anotherposts (userid, time) <- this too
Now, I want to gather the timeline for an user (e.g. userid=4).
Features: Sorted by time DESC, hole offset and limit (this means, if I apply limit = 3 it should not show me 3 posts, 3 otherposts and 3 anotherposts, but the 3 last entries in posts, otherposts, anotherposts [hope you know what i mean])
My try was to make 3 sql statements (lazyloading) and but them in an array.
Afterwards I sort the array by time and delete all entries greater than my limit.
But there occures the problem with the offset clause…
How can I manage to realize that (best with one query, where the offset and limit gets applied).
I need to set the offset and limit to say: OK, first get the entries 1-3 and when the user clicks a more button, it loads 4-6.
Any chance to change the DB design? I know its hard to convey with a simple example.
The problem that I see is that if you try to join tables on userId you will get a cartesian product.
What does a UNION query do for you? You could union 3 tables and get an array in php and sort there.
IE
select userid, time from table1
UNION
select userid, time from table2
UNION
select userid, time from table3
WHERE
userid=$userid and time between 'somedate' and 'otherdate'
To handle pagination you could pass this to CSqlDataProvider and make the pageSize a variable.
No chance to change the db design. Let’s make this example a little bit better: You have on your facebook profiles your posts, comments, likes etc. So there are many tables (one for comments, one for likes, one for posts etc.). And now you want to generate a pinboard (like on facebook), where these things get applied ordered by time…
Is there no chance to get this to work without writing an union statement (but rather with yii methods (find, with, offset, limit etc.)…
