+- 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: Nested Query Question (NEWBIE) (/showthread.php?tid=26441)
Pages:
1
2
Nested Query Question (NEWBIE) - El Forum - 01-13-2010
[eluser]JesseR[/eluser]
Hi,
I am new to CI and having some issues accomplishing what I thought a simple objective.
I need to run some nested queries. Example shown below shows retreiving ITEMS
from the database of one table and then the price from another table. I then
need to attatch the SELLPRIC to the matching record in the array to be able
to display the ITEM and its price in the view.
There are various reasons why I have chosen to do these as completely seperate queries.
I know it can be accomplished in other ways but this is the way we need to do it.
Can anyone advise how to accomplish this and display it? I can't seem to get
SELLPRIC attatched properly to query result array.
Your help is much appreciated.
CONTROLLER:
$this->db->where('CATEGORY','BECAS'/*$this->uri->segment(3)*/);
$this->db->like('DESCRIPT', '1 1/2"');
$data['query_item'] = $this->db->get('ITEMS');
foreach($data['query_item']->result() as $row):
echo $row->ITEMNO;
echo "this is the price:";
/* Get Standard Item Selling Price */
$this->db->select('SELLPRIC');
$this->db->where('ITEMNO',$row->ITEMNO);
$this->db->where('MEAS_TYPE',1);
$data['query_price'] = $this->db->get('ITEM_MEASURE');
foreach($data['query_price']->result() as $row2):
$data['query_item']->SELLPRIC = $row2->SELLPRIC;
echo $row2->SELLPRIC;
echo "<br>";
endforeach;
endforeach;
VIEW:
<?php foreach($query_item->result() as $row): ?>
<h3><?=$row->DESCRIPT?></h3>
<p><?=$row->WEBDESC?></p>
<p><?=$row->SELLPRIC?></p>
<p><?=anchor('blog/comments/'. $row->ITEMNO, 'Comments');?></p>
<hr />
<?php endforeach; ?>
Nested Query Question (NEWBIE) - El Forum - 01-13-2010
[eluser]jedd[/eluser]
Hi JesseR and welcome to the CI forums.
[quote author="JesseR" date="1263440941"]
There are various reasons why I have chosen to do these as completely seperate queries.
I know it can be accomplished in other ways but this is the way we need to do it.
[/quote]
Ask an engineer if the glass is half full, or half empty, and they'll answer 'it's twice as big as it needs to be'. They tend to not like arbitrary conditions wrapped around questions - as it's just a sign of poor design.
Perhaps you could explain why you can't do this properly with a single database call? It would need to be an especially compelling reason to offset the woeful performance-depleting, code-bloating, bug-inducing consequences of looping through queries.
You should also really spend a bit of time - I know it's confusing if you're new to MVC - separating your database queries out into a model. Weirdly enough, this will actually make your intent much easier here. It will also make it much easier to later re-write it as a single, efficient SQL query too. Win-win!
Antepenultimately, rather than doing this (which is pretty confusing code for several reasons)
Code:
$this->db->where('CATEGORY','BECAS'/*$this->uri->segment(3)*/);...do this instead:
Code:
function foo ($param1) {
. . .
$this->db->where('CATEGORY','BECAS'. $param1 );Penultimately, the foreach: / endforeach constructs you're using in the controller look a bit messy - I think they're indicated (though I never use them) more for where you're wrapping up HTML - jumping in and out of <?php ?> partials and the like.
Finally, and similarly, you want to output your data in a view, not as echos in your controller code. Once you separate your HTML generation and data-acquisition into views and models respectively, I think you'll find your problem becomes much easier to solve.
Nested Query Question (NEWBIE) - El Forum - 01-13-2010
[eluser]JesseR[/eluser]
Hi Jedd,
Thanks for your quick response.
Yes I know not to use the echos this was just for troubleshooting when I couldn't get my view to output the array. Also I do plan to use models was just trying to get something working first.
The example I am working on is probably not the best example of code to
explain to you why I can't put it all into one query so I will provide another example.
I am building webstore using a database that can not be modified or changed (its part of existing business system software.). That said I am limited to this exisitng database.
I have a table called ITEMS that displays the ITEM INFORMATION and the default category
it should be displayed under. Then I have a table called ITEM_EQ that will display
the ITEM and an alternate category.
ITEMS
- ITEMID = 123423
- DESCRIPT = PRODUCT
- CATEGORY = BECAS
ITEM_EQ
- ITEMID = 1
- ITEMNO = 123423
- CATEGORY = BECAS
When I run my query to show all items in category BECAS. I need to display records from the ITEMS
table as well as records from the ITEM_EQ table. I can't use a union because the number of fields
being returned are not the same. Secondly... unions can take a lot longer and thirdly I need
to perform some if logic to change values of the results returned depending on the values
in certain fields in the database.
Once again I appreciate any further advise you can provide. If you need more information
from me to advise just let me know.
Nested Query Question (NEWBIE) - El Forum - 01-13-2010
[eluser]jedd[/eluser]
Okay .. well, I think we all do output in controllers for debugging - and you said earlier that your data 'wasn't quite right' (or words) so perhaps a var_dump / print_r of the array that you're generating would be useful to get an idea of where it's going wrong as you build it. (If you post it, or more code, remember to use the [ code ] tags to make it easier to read).
With your DB:
Code:
ITEMS
- ITEMID = 123423
- DESCRIPT = PRODUCT
- CATEGORY = BECAS
ITEM_EQ
- ITEMID = 1
- ITEMNO = 123423
- CATEGORY = BECASAm I right in assuming that item_eq.itemno = items.itemid - as in a foreign key (or a pretend one, at least).
Why can't you just do a JOIN here? If I'm wrapping my head around your problem properly (and I may well be missing something here - been a big day) it would probably also be easier for the view generation too. Is something like this thread - [url="http://ellislab.com/forums/viewthread/136469"]http://ellislab.com/forums/viewthread/136469[/url] - kind of where you're heading with this? That thread also links back to a [url="http://ellislab.com/forums/viewthread/125879"]this thread[/url] which is pretty similar.
Quote:... thirdly I need to perform some if logic to change values of the results returned depending on the values in certain fields in the database.
Yeah - this could be a tad challenging. Is it feasible to apply this logic post-query?
Nested Query Question (NEWBIE) - El Forum - 01-13-2010
[eluser]JesseR[/eluser]
HERE IS MY CONTROLLER:
Code:
<?php
class Catalog extends Controller {
function Catalog()
{
parent::Controller();
/* $this->load->scaffolding('entries');*/
$this->load->helper('url');
$this->load->helper('form');
}
function index()
{
$data['query'] = $this->db->get('entries');
}
function products()
{
$this->db->where('CATEGORY','BECAS'/*$this->uri->segment(3)*/);
$this->db->like('DESCRIPT', '1 1/2"');
$data['query_item'] = $this->db->get('ITEMS');
foreach($data['query_item']->result() as $row):
$items['itemno'] = $row->ITEMNO;
$items['descript'] = $row->DESCRIPT;
echo "test";
/* Get Standard Item Selling Price */
$this->db->select('SELLPRIC');
$this->db->where('ITEMNO',$row->ITEMNO);
$this->db->where('MEAS_TYPE',1);
$data['query_price'] = $this->db->get('ITEM_MEASURE');
foreach($data['query_price']->result() as $row2):
$items['descript'] = $row2->SELLPRIC;
endforeach;
$itemsarray[] = $items;
$data = $itemsarray;
endforeach;
$this->load->view('product_view', $data);
}
}
?>Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Product Page></title>
</head>
<body>
<ol>
<?php foreach($data->result() as $row): ?>
<h3><?=$row->DESCRIPT?></h3>
<p><?=$row->WEBDESC?></p>
<p><?=$row->SELLPRIC?></p>
<p><?=anchor('blog/comments/'. $row->ITEMNO, 'Comments');?></p>
<hr />
<?php endforeach; ?>
</ol>
</body>
</html>[quote author="jedd" date="1263446790"]
Am I right in assuming that item_eq.itemno = items.itemid - as in a foreign key (or a pretend one, at least).
Why can't you just do a JOIN here? If I'm wrapping my head around your problem properly (and I may well be missing something here - been a big day) it would probably also be easier for the view generation too. Is something like this thread - [/quote]
You are correct ITEM_EQ.ITEMNO = ITEMS.ITEMID. Any record in the ITEM_EQ table will always have a matching record in the ITEMS table. However there are various fields in each table such as CUSTMEMO1, etc... I need to retrieve. If the field CUSTMEMO1 is blank in the ITEM_EQ table I would have to display the field from ITEMS table.
I also need to retrieve the pricing information. If the field ITEM_EQ.SELLPRIC is true I need to use the items original sell price retrieved from a different table with different where arguments but if it is false i would display the price as the value in ITEM_EQ.SELLINGPRICE.
This is just a few examples I hope will make it more clear of my objectives here.
Once again I appreciate your help and look forward to your response.
[quote author="jedd" date="1263446790"]
Yeah - this could be a tad challenging. Is it feasible to apply this logic post-query?[/quote]
What do you mean post-query? Isn't that what I am referring to when I say I want to modify the returned query array results. Maybe I misunderstand the question. Please clarify.
Nested Query Question (NEWBIE) - El Forum - 01-14-2010
[eluser]sl3dg3hamm3r[/eluser]
[quote author="JesseR" date="1263467142"]I also need to retrieve the pricing information. If the field ITEM_EQ.SELLPRIC is true I need to use the items original sell price retrieved from a different table with different where arguments but if it is false i would display the price as the value in ITEM_EQ.SELLINGPRICE.[/quote]
Is this the reason why you loop and do queries in a loop? Like Jedd, I also can't follow why you do it that way. In that case, I would do LEFT OUTER - joins, containing whatever where-statement you need. If ITEM_EQ.SELLPRIC is false, the correspondending row might be NULL, but you still would have all your data.
If you have some heavy logic for retrieving data from a result-set (if field x = true, take field y etc.), I would encapsulate the query-object in an object (implement iterator), and encapsulate the logic there. This is in the sense of OOP, combine data and behavior in one place. In other words: Instead of returning the query-object in your model (which you don't have yet), let the model save the query and return the fields you need, implementing your mentioned logic. Since you would implement then the iterator, you would be able to use foreach on the model itself. Very handy.
Nested Query Question (NEWBIE) - El Forum - 01-14-2010
[eluser]JesseR[/eluser]
[quote author="sl3dg3hamm3r" date="1263483317"]
If you have some heavy logic for retrieving data from a result-set (if field x = true, take field y etc.), I would encapsulate the query-object in an object (implement iterator), and encapsulate the logic there. This is in the sense of OOP, combine data and behavior in one place. In other words: Instead of returning the query-object in your model (which you don't have yet), let the model save the query and return the fields you need, implementing your mentioned logic. Since you would implement then the iterator, you would be able to use foreach on the model itself. Very handy.[/quote]
I don't completely follow. Would you mind providing an example of what you mean? I'm
not really versed in OOP. I have always just got by doing php with a non oop style.
[quote author="sl3dg3hamm3r" date="1263483317"]
In other words: Instead of returning the query-object in your model (which you don't have yet), let the model save the query and return the fields you need, implementing your mentioned logic. Since you would implement then the iterator, you would be able to use foreach on the model itself. Very handy.[/quote]
Are you saying to put my if statements in the view where my foreach displays or running a foreach in another file and then another foreach in my view?
Nested Query Question (NEWBIE) - El Forum - 01-14-2010
[eluser]sl3dg3hamm3r[/eluser]
Ok, here comes some really constructed example. Let's say you have a table 'Country' with two columns 'Id' and 'Code' (aka country-code). This example is now little bit overcomplicated, but I think I would do something similar in your case:
Code:
<?php
/*
* Created on 14.01.2010
*
* To change the template for this generated file go to
* Window - Preferences - PHPeclipse - PHP - Code Templates
*/
class Mdl_country extends Model implements Iterator {
private $query = null; // Sql-query result array
private $position = 0; // Pointer for Iterator
public function __construct()
{
parent::__construct();
}
/**
* Loads data into var
*/
public function loadData()
{
$this->query = $this->db->get('Country');
$this->query = $this->query->result_array();
}
/**
* HERE IS DEFINED SOME SPECIAL BUSINESS-LOGIC: IF THE CODE IS 'CH', EMPHASIZE IT!
*/
public function getCode()
{
$field = $this->query[$this->position]['Code'];
if ($field == 'CH')
return "CH ROCKS!";
return $field;
}
// Iterator-Interface
function rewind() {
$this->position = 0;
}
// Iterator-Interface
function current() {
return $this->query[$this->position];
}
// Iterator-Interface
function key() {
return $this->query[$this->position];
}
// Iterator-Interface
function next() {
++$this->position;
}
// Iterator-Interface
function valid() {
if ($this->query != null && isset($this->query[$this->position]))
return true;
return false;
}
}In the controller (this should be done in a view, but for the sake of having it simple:
Code:
function index()
{
$this->load->model('mdl_country', '', true);
$this->mdl_country->loadData();
foreach($this->mdl_country as $row)
echo $row['Id'] . ": " . $this->mdl_country->getCode() . '<br />';
}As you can see, in order to print the country-code, I ask a specialised method 'getCode()'. In there I check what is written in the field and slightly modify it if a given rule matches.
In your case this method could be called 'getPrice()', where you could implement your own logic (if field is null, take another one etc.).
The advantage: Data and behaviour is kept together within one class. The view won't need to know any logic. You would just call the method(s) of your class, the rest is done there.
Nested Query Question (NEWBIE) - El Forum - 01-14-2010
[eluser]JesseR[/eluser]
Quote:class Mdl_country extends Model implements Iterator {
Is Implements Iterator the name of the model you made or
is this an actual function built into CI?
Quote:As you can see, in order to print the country-code, I ask a specialised method ‘getCode()’. In there I check what is written in the field and slightly modify it if a given rule matches.
In your case this method could be called ‘getPrice()’, where you could implement your own logic (if field is null, take another one etc.).
So I would have to create a special function for each field?
Example: I have an ITEM DESCRIPTION, LONG DESCRIPTION in both items and item_eq table.
If the value in the ITEM_EQ table is empty for any fields then we use the value of
the matching record in the ITEMS table.
Nested Query Question (NEWBIE) - El Forum - 01-14-2010
[eluser]JesseR[/eluser]
With your help I am beginning to make a little progress in my
testing stages. Heres what I have. I am a little confused about the
whole Iterator-Interface and the different actions (rewind, current, key, etc...)
Is there any documentation on this that further explains this function/feature
so I can better understand what its doing. I see it working but dont completely
understand what it does.
Let me know what you think of my code thus far and any pointers you might
have.
MODEL:
Code:
<?php
/*
* Created on 14.01.2010
*
* To change the template for this generated file go to
* Window - Preferences - PHPeclipse - PHP - Code Templates
*/
class TestM extends Model implements Iterator {
private $query = null; // Sql-query result array
private $position = 0; // Pointer for Iterator
public function __construct()
{
parent::__construct();
}
/**
* Loads data into var
*/
public function loadData()
{
// $this->db->select('ITEMNO,DESCRIPT AS ITEM_DESCRIPT,CATEGORY AS CATEGORY_CODE,.DESCRIPT AS CATEGORY_DESCRIPT');
// $this->db->from('ITEMS');
// $this->db->join('CATEGORY', 'CATEGORY.CATEGORY = ITEMS.CATEGORY');
// $this->db->where('CATEGORY','BECAS'/*$this->uri->segment(3)*/);
$this->db->like('DESCRIPT', '1 1/2"');
// $this->db->limit(10);
$this->query = $this->db->get('ITEMS');
$this->query = $this->query->result_array();
}
/**
* HERE IS DEFINED SOME SPECIAL BUSINESS-LOGIC: IF THE CODE IS 'CH', EMPHASIZE IT!
*/
public function getCategory()
{
$field = $this->query[$this->position]['CATEGORY'];
if ($field == 'BECAS')
return "CH ROCKS!";
return $field;
}
public function getPrice()
{
$field = $this->query[$this->position]['ITEMNO'];
$this->db->select('SELLPRIC');
$query = $this->db->get_where('ITEM_MEASURE', array('ITEMNO' => $field,'MEAS_TYPE' => '1'));
$row = $query->row();
return $row->SELLPRIC;
}
// Iterator-Interface
function rewind() {
$this->position = 0;
}
// Iterator-Interface
function current() {
return $this->query[$this->position];
}
// Iterator-Interface
function key() {
return $this->query[$this->position];
}
// Iterator-Interface
function next() {
++$this->position;
}
// Iterator-Interface
function valid() {
if ($this->query != null && isset($this->query[$this->position]))
return true;
return false;
}
}CONTROLLER:
Code:
<?php
class Test extends Controller {
function Test()
{
parent::Controller();
}
function index()
{
$this->load->model('TestM', '', true);
$this->TestM->loadData();
foreach($this->TestM as $row)
echo $row['ITEMNO'] . ": " . $this->TestM->getCategory() . $this->TestM->getPrice() . ": " . '<br />';
}
}