SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pulling in data from related entries.

    First off I apologize for the extremely vague title, not too sure how to describe what I'm up against in a short phrase.

    I have a series of 3 queries I need to display entry data within a category and assigned to one client.

    For the most part I've successfully established the first 2 queries with one issue. I need to call all entries with a t.status = 'open' and t.status = 'featured' but I'm not too sure how to.

    I've tried AND/OR but this has not worked. Any advice?
    Code MySQL:
    *********************************************** 
    All Entries for one Client
    *********************************************** 
    SELECT t.title
    	 , t.url_title
    	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = "5") as Client
    	 // 5 is the value of a dynamic variable
    FROM exp_weblog_titles AS t 
    LEFT 
      JOIN exp_weblog_data AS d 
    	ON t.entry_id = d.entry_id
       AND t.status = 'open'
    LEFT  
      JOIN exp_relationships as r
    	ON d.entry_id =  r.rel_parent_id
     WHERE r.rel_child_id = '5' // 5 is the value of a dynamic variable 
     ORDER 
        BY t.entry_date 
    DESC
    Code MySQL:
    *********************************************** 
    All Entries for one Client within selected category
    *********************************************** 
    SELECT t.title
    	 , t.url_title
    	 , c.cat_url_title
    	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = "5") as Client
    	 // 5 is the value of a dynamic variable
      FROM exp_categories AS c 
    INNER 
      JOIN exp_category_posts AS cp 
    	ON cp.cat_id = c.cat_id 
       AND c.cat_url_title = 'corporate_video' 	 // corporate_video is the value of a variable
    INNER 
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND t.status = 'open'
    LEFT 
      JOIN exp_weblog_data AS d 
    	ON t.entry_id = d.entry_id
    LEFT 
      JOIN exp_relationships as r
    	ON d.entry_id =  r.rel_parent_id
     WHERE r.rel_child_id = '5' // 5 is the value of a dynamic variable
     ORDER 
        BY t.entry_date 
    DESC
    My trouble is with this last query. I need to show a list of all Clients who have
    projects within one category but I only want to list the client name once.

    The difficulty is with how the "Client information" is stored within the CMS DB.
    I'm dealing with the systems architecture which allows me to create relationships between entries which enables me to pull in data from another entry. These relationships are defined in their own table but the data is stores in different tables.

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hothousegraphix View Post
    I need to call all entries with a t.status = 'open' and t.status = 'featured'
    So, it looks like I need to add "OR" to my AND to evaluate multiple values for one variable.
    Code MySQL:
       AND (t.status = 'open' OR t.status = "featured")
    Still trying to resolve the last query.

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, so my last query, I need to find all entries within a selected "Category" and then group by the "Client Name" and then only return the most recent of that set.

    My problem lies with the fact that the "Client Name" is defined via a relationship that uses id's to tie two different entries together.

    Starting off I can bring in all my entries within a selected "Category"
    Code MySQL:
    SELECT t.title
    	 , t.url_title
    	 , c.cat_url_title
    	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_parent_id) as Client
      FROM exp_categories AS c 
    INNER 
      JOIN exp_category_posts AS cp 
    	ON cp.cat_id = c.cat_id 
       AND c.cat_url_title = 'corporate_video' 
    INNER 
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND (t.status = 'open' OR t.status = "featured")
    LEFT
      JOIN exp_weblog_data AS d 
    	ON t.entry_id = d.entry_id
    LEFT 
      JOIN exp_relationships as r
    	ON d.entry_id =  r.rel_parent_id
     ORDER 
        BY t.entry_date 
    DESC
    Works like a charm but now I'd like to tackle showing my client name:
    Code MySQL:
    SELECT t.title
    	 , t.url_title
    	 , c.cat_url_title
    	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_parent_id) as client
      FROM exp_categories AS c 
    INNER 
      JOIN exp_category_posts AS cp 
    	ON cp.cat_id = c.cat_id 
       AND c.cat_url_title = 'corporate_video' 
    INNER 
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND (t.status = 'open' OR t.status = "featured")
    LEFT
      JOIN (SELECT MAX(entry_id) as max 
    		  FROM exp_weblog_data 
    		 GROUP 
    			BY (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_parent_id)) as ids 
    // sub query used to get at table that contains client_name 
    	ON t.entry_id = ids.max 
    LEFT 
      JOIN exp_weblog_data AS d 
    	ON t.entry_id = d.entry_id
    LEFT 
      JOIN exp_relationships as r
    	ON d.entry_id =  r.rel_parent_id
     ORDER 
        BY t.entry_date 
    DESC
    However, this is showing all data other than the "client name" and because that is not present, I'm not seeing the the LEFT JOIN to my sub-query reducing the data down to just one "client name" within the selected category.

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is anyone able to assist?

    Thanks

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I'm still very interested in seeing if anyone would be kind enough to assist.

    I'm about 90% where I need to be. I have the query grabbing all entries for a single client within a category and then reducing down to only one entry; however, my intent is to return the most recent of all client entries. This is not happening.

    Code MySQL:
    SELECT t.title
    	 , t.url_title
    	 , date_format(t.entry_date, '%d/%m/%Y') AS date
    	 , c.cat_url_title
    	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_child_id) as Client
      FROM exp_categories AS c 
    INNER 
      JOIN exp_category_posts AS cp 
    	ON cp.cat_id = c.cat_id 
       AND c.cat_url_title = 'corporate_video' 
    INNER 
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND (t.status = 'open' OR t.status = "featured")
    INNER
      JOIN (SELECT MAX(rel_id) as max, rel_parent_id
    		  FROM exp_relationships 
    		 GROUP 
    			BY rel_child_id) as ids
    	ON t.entry_id = rel_parent_id
    LEFT 
      JOIN exp_weblog_data AS d 
    	ON t.entry_id = d.entry_id
    LEFT 
      JOIN exp_relationships as r
    	ON d.entry_id =  r.rel_parent_id
     ORDER 
        BY Client 
    ASC
    Any tips to achieve what I'm looking to do?

    Thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •