Smarty Forum Index Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon.
  www.smarty.net   •  phpinsider.com  •  Forum Index  •  FAQ  •  Search  •  Memberlist  •  Profile  •  Log in to check your private messages  •  Register  •  Log in
One-to-many tables relationship

 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> General
View previous topic :: View next topic  
Author Message
simon3z
Smarty n00b


Joined: 08 Dec 2007
Posts: 2
PostPosted: Sat Dec 08, 2007 6:00 pm    Post subject: One-to-many tables relationship Reply with quote
Hi all, I'm wondering what's the best practice to retrieve and display the data of a one-to-many relationship.
Let's take a common case as example: product_category has-many products. How can I display the categories along with the products?
Let's assume I have a large number of product categories and I paginate them with 20 results per page, that would become 20 queries on the products table to find the related items.
I could perform a join on the two tables but I dont want to repeat the product_category for each product I display; I would like the name of the product_category followed by the list of products. Example:

_ Category1
__ Prod1Cat1
__ Prod2Cat1
_ Category2
__ Prod1Cat2
_Categotry3
__ Prod1Cat3
[...]

Is there a common practice for this?

Thank you in advance.
Back to top
View user's profile Send private message
czarnowski
Guest
PostPosted: Sun Dec 09, 2007 11:40 am    Post subject: Reply with quote
assign a blank value to smarty variable oldcat before the beginning of the foreach

compare the actual cat with oldcat

if not the same value assign the new one to oldcat and print it out
Back to top
master_kaos
Smarty Regular


Joined: 02 Aug 2007
Posts: 54
PostPosted: Tue Dec 11, 2007 3:45 pm    Post subject: Reply with quote
I am actually working on exactly this this morning (just taking my morning break)
this is how I am doing it (sqlFunctions is my own SQL helper class)

Code:

class category
{
   var $id = null;
   var $name = null;
   var $sql;
   var $products;
   function category(id)
   {
      global $db;
      $this->sql = new sqlFunctions($db);
      $results = $sql->select("SELECT * FROM category WHERE id = '" .       $this->sql->clean($id) . "'");      
      $this->id = $results[0]['id'];
      $this->name = $results[0]['name'];
      $this->products = product::getByCategoryID($this->id);
   }

   function getAll()
   {
      global $db;
      $sql = new sqlFunctions($db);
      $categories = array();
      $results = $sql->select("SELECT id FROM category");
      foreach($results as $result)
      {
         $categories[] = new category($result['id']);
      }
      return $categories;
   }

}

class product
{
   var $id = null;
   var $name = null;
   var $sql;
   
   function product(id)
   {
      global $db;
      $this->sql = new sqlFunctions($db);
      $results = $sql->select("SELECT * FROM product WHERE id = '" .       $this->sql->clean($id) . "'");      
      $this->id = $results[0]['id'];
      $this->name = $results[0]['name'];
   }
   function getByCategoryID($id)
   {
      global $db;
      $sql = new sqlFunctions($db);
      $products = array();
      results = $sql->select("SELECT id FROM product WHERE id = '" . $sql->clean($id). "'");
      foreach($results as $result)
      {
         $products[] = new product($result['id']);
      }
      return $products
   }
}



now php code could be like this
$smarty->assign('categories', category::getAll());


then template would be like this

{foreach from=$categories item=category}
   {$category->name}<br>
   {assign var=products value=$categories->products}
   {foreach from=$products item=product}
      &nbsp;&nbsp;&nbsp;{$product->name}<br>
   {/foreach}
{/foreach}




I haven't actually tested this code, but it will work, might be 1 or two small typos though
Back to top
View user's profile Send private message
simon3z
Smarty n00b


Joined: 08 Dec 2007
Posts: 2
PostPosted: Tue Dec 11, 2007 4:19 pm    Post subject: Reply with quote
Thank you very much! Your code is very clean and I really like the getAll and getByCategoryID idea. Very well implemented!
I have to say that I am quite concerned about the amount of the sql queries it would generate:

1 query: SELECT id FROM category;
N queries: SELECT * FROM category WHERE id = '?';
N queries: SELECT id FROM product WHERE id = '?';
M queries: SELECT * FROM product WHERE id = '?';

Where N is the number of the categories and M is the number of the products in each category.
To retrieve just 1 product in 1 category I would have 4 sql queries. To retrieve 2 products in 2 different categories would be 7 sql queries. (Please correct me if my math is wrong).

Do you think this would be scalable? Can we trade performances for clean code?
Do you think that your solution would scale even on a high traffic site?

Thank you for your answer.
Back to top
View user's profile Send private message
master_kaos
Smarty Regular


Joined: 02 Aug 2007
Posts: 54
PostPosted: Tue Dec 11, 2007 5:07 pm    Post subject: Reply with quote
I have never really worked on an application that has high load, so (extreme) performance tweaks was never really an issue.

One thing you could do however, is take out the $products variable, and then just do a manual call on getByCategoryID.

(Smarty can't call a static function on a class, so I just assign a blank product)


Code:

$smarty->assign('product', new product(null));



{foreach from=$categories item=category}
   {$category->name}<br>
{assign var=products value=$product->getByCategoryID($smarty.get.id)}   
   {foreach from=$products item=product}
      &nbsp;&nbsp;&nbsp;{$product->name}<br>
   {/foreach}
{/foreach}




This way it won't preload the class, so you won't have the extra overhead if you don't need it


But yeah, still a lot of sql queries. I have never had to worry about it because I haven't had much load on my web apps, but if I did and I saw performance dropping, I may seek another solution.

What you could maybe do to drop a few queries is have a function that JUST returns raw sql results
so something like
Code:

//can't think of a good name right now
function getAllRaw()
{
   global $db;
   $sql = new sqlFunctions($db);
   return $sql->select("SELECT * FROM category");
}





This will be WAY quicker, and way less sql queries.
You can then access the data by doing

{$product.name}
You just wont have all the class information available, just the raw results. BUt if all you are doing is just displaying it, it probably doesn't matter.




edit::: Now that I think about it , I actually did do this solution a couple projects back. A client wanted to display 6000 products on their screen at once without pagination. And creating all the classes and running all the SQL queries took a while (only a second or two, but still that is way too slow)
so I did this solution and it was really fast.
Back to top
View user's profile Send private message
Display posts from previous:   
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> General All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP