Modular Extensions and multiple queries

Modular Extensions and multiple queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Modular Extensions and multiple queries (/showthread.php?tid=9253)

Modular Extensions and multiple queries - El Forum - 06-18-2008

[eluser]Jim Higgins[/eluser]
Hi. I've been using HMVC Modular Extensions for a real estate app that I'm building. Basically, on all pages, I have a "search listings" panel and a "saved listings" panel which allow the user to, respectively, search listings and, if they have saved listings, display mini-thumbnails in the saved listings panel.

I am using Modular Extensions for the Saved Listings panel... it's a module that checks if the user is logged in, if they're and, if they are logged in, it queries the saved_listings table to fetch their saved listings (if they have any). If they're not logged in, it just says "you must login to view saved listings". If they have no saved listings, it just says "you have 0 saved listings".

So, basically, every time a user runs a search through the search panel and it loads a new page... like search results... the Saved Listings module takes care of it's little business on the side (checks for login and fetches saved listings). In doing so, there are moments when, essentially, it runs 2 queries on different tables. The search panel will query the listings table and the saved listings module might query the users table to fetch a login.

This was all working perfectly fine for a very long time with no problems... until now. Recently, I changed up the search panel functionality so that it can run a join on 2 different db tables... a "listings" table and an "addresses" table. Problem is, since implementing the join in the search panel, it combines the query on the user table with the joined query on the the addresses and listings table.

I kind of understand why this is happening. There is one instance of the database and it runs those queries at the same time as one big query. I'm just not sure how to get around this.

Here are a couple snippets of code. Inside of my search model, I run this query...

Code:
$this->db->select('

    addresses.street1,

    addresses.street2,

    addresses.city,

    addresses.state,

    addresses.zip,

    addresses.type,

    listings.id, 

    listings.seller_id, 

    listings.address_id,   

    listings.price, 

    listings.property_type, 

    listings.bedrooms, 

    listings.bathrooms, 

    listings.sqft, 

    listings.acres, 

    listings.quicktag, 

    listings.lat, 

    listings.lng

');

    // From

        $this->db->from('addresses');

    // Join with listings

    $this->db->join('listings', 'listings.address_id = addresses.id');

        // Get

    $data['listings'] = $this->db->get();

I also have some additional where clauses, an order by, some stuff like that between the join and the get, but left it out for simplicity.

My other snippet is from my user model. My saved listings module calls this to get the login info before it displays any saved listings for a user (it needs to check that the user is logged in)...

Code:
// Get data based on id passed

$this->db->select('

    id,

    email,

    password,

    created_date,

    comm_options,

    last_login,

    access_level,

    active

');

$this->db->where('id', $id);

$query = $this->db->get('users', 1);

So, if a user is not logged in. Everything runs fine because the saved listings module never runs the additional query on the db. However, as soon as someone is logged in and it needs to query the user data, I get a MySql error that shows the giant sql statement combined as one. This does not happen if, in the search model, I only query one table... like the listings table by itself... and don't run the join. Here is the query that shows when the error displays...

Code:
A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(listings.property_type='1' OR listings.property_type='2' OR listings.property_t' at line 5

SELECT `id`, `email`, `password`, `created_date`, `comm_options`, `last_login`, `access_level`, `active`, addresses.street1, addresses.street2, addresses.city, addresses.state, addresses.zip, addresses.type, listings.id, listings.seller_id, listings.address_id, listings.price, listings.property_type, listings.bedrooms, listings.bathrooms, listings.sqft, listings.acres, listings.quicktag, listings.lat, listings.lng FROM (`users`, `addresses`) JOIN `listings` ON listings.address_id = addresses.id WHERE `id` = '1' (listings.property_type='1' OR listings.property_type='2' OR listings.property_type='3') LIMIT 1

As you can see, it's combining the query for the search with the query to get the user data.

Modular Extensions and multiple queries - El Forum - 06-18-2008

[eluser]Jim Higgins[/eluser]
I figured out what my problem was. While working on my query with the join, I had commented out my flush_cache() statement after calling the get. So, in short, after the first query ran, the cache was interfering with the query to get the login data.

So, I started the cache, ran the query, stopped the cache, but was never flushing that cache and, since the Modular Extension query was running immediately after, it was... well, I don't really know what exactly it was doing, but it was producing a pretty wacky query.