views:

45

answers:

1

alt text

example if i have

follow table
company one ( cid = 1 ) following two ( cid = 2 )
company one ( cid = 1 ) following three( cid = 3 )

feeds table
company one ( cid = 1 ) type 'product' description 'hello ive updated a product';
company two ( cid = 2 ) type 'product' description 'hello ive updated a product im from company 2';
company three ( cid = 3 ) type 'shoutout' description 'hello ive i got a shoutout im company 3';
company one ( cid = 1 ) type 'product' description 'hello ive updated my second product';

question

how do i get all the feeds.description from the company that my company ( example here is cid = 1 ) ?

heres my simple pdo sql to get just mine.

    $data['feeds']['cid'] = 1;

    return $this->db->fetchAll("SELECT feeds.type, feeds.$type, feeds.cid, feeds.time, companies.name FROM feeds, companies WHERE 
    feeds.cid = :cid AND companies.cid = :cid ORDER BY feeds.fid DESC LIMIT 0, 5", $data['feeds']);

this will display
hello ive updated a product
hello ive updated my second product

maybe something like this ? ( fail )

    $data['feeds']['cid'] = 1,2,3;

    return $this->db->fetchAll("SELECT feeds.type, feeds.$type, feeds.cid, feeds.time, companies.name FROM feeds, companies WHERE 
    feeds.cid = :cid AND companies.cid = :cid ORDER BY feeds.fid DESC LIMIT 0, 5", $data['feeds']);

this should be displaying like
hello ive updated a product
hello ive updated a product im from company 2
hello ive i got a shoutout im company 3
hello ive updated my second product

or simpler get feeds.description from each follow.following ( cid = 1,2,3,etc ) from me. ( cid = 1) or if twitter get all my friends status ( friends that i follow )

edit*

some good guys at irc mysql said to use joins. but i just dont get it. what i get is this

fetch all the follow.following from cid = me ( example cid = 1 )

then

SELECT * FROM feeds WHERE feeds.cid = IN (2,3,cid that im following ( see follow.following )) 

anyone can give me an example for joins in this problem ?

+1  A: 

So, assuming that the question is "How do i get all the feeds.description from the company that my company IS FOLLOWING?" you want to join using the following key:

select 
companies.name, 
feeds.type, 
feeds.description 
from follow 
inner join feeds on (follow.following = feeds.cid or follow.cid = feeds.cid)
inner join companies on (feeds.cid = companies.cid)
where follow.cid = :cid
order by feeds.fid desc 
limit 5;

This should list all of the companies that your :cid (1) is following.

EDIT: Feeds should include my company's feeds + feeds my company is following.

zevra0
but one more problem, where is our own feeds ?
Adam Ramadhan
So you're looking for your own company's feeds + the feeds that your company is following?
zevra0
yes!, btw nice one. if i could i give you +50 :| but i have to wait 2 days. anyway some said that my database scema will not scale.
Adam Ramadhan
BTW... no problem. You post an ERD you deserve an answer.
zevra0
in the name of kernel. how do i never think of that ( i was trying 3 hours already.
Adam Ramadhan
anyway do i need improvement to the erd ?
Adam Ramadhan
It's hard to say from the question. One thing I would definitely change is to make feeds.following a foreign key to companies (add an index, at least). How many companies / products / feeds are you expecting?
zevra0
quite a lot. btw when we add fk its an index too isnt? btw yes ive add an fk, see the bottom feeds.company.uid_FK it means that feeds.cid is a FK to companies.cid
Adam Ramadhan
No, the following column is not indexed according to your ERD...alter table follow add foreign key (following) references companies(CID);
zevra0
ah yes. i missed that one :D thanks again. god bless.
Adam Ramadhan