Notifications listings

[eluser]JamieBarton[/eluser]
Hi guys,

I have a wall type thing on my website's dashboard, I want users to be able to see actions other users have done (these are stored inside a notifications table).

I'm having problems though, I think it has something to do with my joins.

In my table I have :

Code:

`notification_id` bigint(20) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `is_photo` int(1) NOT NULL default '0',
  `photo_id` bigint(20) NOT NULL default '0',
  `is_album` int(1) NOT NULL default '0',
  `album_id` bigint(20) NOT NULL default '0',
  `is_wall` int(1) NOT NULL default '0',
  `wall_id` bigint(20) NOT NULL default '0',
  `family_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`notification_id`)

These will just use 1 and 0 for the is_ and the _id's will hold the ID of the corrosponding photo/album/wallpost id.

My Query in my helper is:

Code:

function family_notifications( $fid, $lmt )
    {
        $this->db->where('notifications.family_id', $fid);
        $this->db->join('users', 'users.user_id = notifications.user_id');
        $this->db->join('photos', 'photos.photo_id = notifications.photo_id');
        $this->db->join('albums', 'albums.album_id = notifications.album_id');
        
        $this->db->order_by('notification_id', 'desc');
        $this->db->limit($lmt);
            return $this->db->get('notifications');
    }

Perhaps you guys can help out, if I remove the JOINS, and put in dummy data it works.


Regards,

Jamie

[eluser]jedd[/eluser]
[quote author="Jamie B" date="1261192417"]
My Query in my helper is:
[/quote]

Why are you doing (these kinds of) queries in your helper?

With the code you supplied - if you're designing this from the AR side and then diagnosing it, it's good to turn on Profiling so you can see the actual SQL query generated. You can then plug that into the SQL CLI and work with it until you get the results you want, and then convert it back to AR form.

[eluser]JamieBarton[/eluser]
Sorry , my helper loads the notifications model.

Code:

$notif = $CI->m_notifications->family_notifications( $fid, $lmt );

I'm not 100% sure on JOINS, and if those are correct the way I've formatted by query above.

[eluser]jedd[/eluser]
[quote author="Jamie B" date="1261192417"]
Perhaps you guys can help out, if I remove the JOINS, and put in dummy data it works.
[/quote]

I think perhaps this is the confusing bit.

If it works when you take the JOINs out, why don't you just take the JOINs out?

Perhaps you mean 'it doesn't work, but in a different way to the way it doesn't work with the JOINs'. At which point the question becomes 'what doesn't work about the JOIN's, compared to, say, without them'.

Can you run the Profiler to see what your raw SQL looks like (and post it) - that's often instructive when you're trying to get your head around new SQL constructs.

[eluser]Zack Kitzmiller[/eluser]
[quote author="jedd" date="1261196827"][quote author="Jamie B" date="1261192417"]
Perhaps you guys can help out, if I remove the JOINS, and put in dummy data it works.
[/quote]

I think perhaps this is the confusing bit.

If it works when you take the JOINs out, why don't you just take the JOINs out?

Perhaps you mean 'it doesn't work, but in a different way to the way it doesn't work with the JOINs'. At which point the question becomes 'what doesn't work about the JOIN's, compared to, say, without them'.

Can you run the Profiler to see what your raw SQL looks like (and post it) - that's often instructive when you're trying to get your head around new SQL constructs.[/quote]

Agreed. The code that you've posted looks good, (except not checking to see if you have rows before returning them.)