• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
INNER JOIN datatable sintax

#1
[eluser]webmarkcompt[/eluser]
Hello everyone,
I am dealing with a sintax problem
I have this code working
Code:
SELECT
p.id, p.name,
p.code,
clA.code as claCode,
sclA.code as sclaCode,
clB.code as clbCode,
sclB.code as sclbCode,
clC.code as clcCode,
sclC.code as sclcCode

FROM
             products p
                 INNER JOIN catleathers clA
                    ON p.catleather_id_a = clA.id
                 INNER JOIN subcatleathers sclA
                    ON p.subcatleather_id_a = sclA.id
                 INNER JOIN catleathers clB
                    ON p.catleather_id_b = clB.id
                 INNER JOIN subcatleathers sclB
                    ON p.subcatleather_id_b = sclB.id
                 INNER JOIN catleathers clC
                    ON p.catleather_id_c = clC.id
                 INNER JOIN subcatleathers sclC
                   ON p.subcatleather_id_c = sclC.id

But I need something like this, and is not working

Code:
function getdatatableajaxcost()
   {

    $this->load->library('datatables');
    $this->datatables
  
  ->select("
  
   products.id as productid,
   products.image as image,
   products.code as code,
   products.name as name,
   categories.name as cname,
   subcategories.name as subcategory,
   products.cost, products.price,  
   products.lining as lining,
   COALESCE(quantity, 0) as quantity,
   alert_quantity,
  
    clA.code as claCode,
                sclA.code as sclaCode,
                clB.code as clbCode,
                sclB.code as sclbCode,
                clC.code as clcCode,
                sclC.code as sclcCode,

                catSoleA.code as catSoleACode,
                subcatSoleA.code as subcatSoleACode,

                catSoleB.code as catSoleBCode,
                subcatSoleB.code as subcatSoleBCode,

                catSoleC.code as catSoleCCode,
                subcatSoleC.code as subcatSoleCCode,
            
   products.details", FALSE)
  
   ->from('products')
   ->join('categories', 'products.category_id=categories.id', 'left')
   ->join('subcategories', 'products.subcategory_id=subcategories.id', 'left')
  
   ->join('catleathers clA', 'products.catleather_id_a = clA.id', 'INNER')
            ->join('subcatleathers sclA', 'products.subcatleather_id_a = sclA.id', 'INNER')

            ->join('catleathers clB', 'products.catleather_id_a = clB.id', 'INNER')
            ->join('subcatleathers sclB', 'products.subcatleather_id_a = sclB.id', 'INNER')

            ->join('catleathers clC', 'products.catleather_id_a = clC.id', 'INNER')
            ->join('subcatleathers sclC', 'products.subcatleather_id_a = sclC.id', 'INNER')

            ->join('catsoles catSoleA', 'products.catsole_id_a = catSoleA.id', 'INNER')
            ->join('subcatsoles subcatSoleA', 'products.subcatsole_id_a = subcatSoleA.id', 'INNER')

            ->join('catsoles catSoleB', 'products.catsole_id_b = catSoleB.id', 'INNER')
            ->join('subcatsoles subcatSoleB', 'products.subcatsole_id_b = subcatSoleB.id', 'INNER')

            ->join('catsoles catSoleC', 'products.catsole_id_c = catSoleC.id', 'INNER')
            ->join('subcatsoles subcatSoleC', 'products.subcatsole_id_c = subcatSoleC.id', 'INNER')

   ->join('tbl_lining', 'products.lining=tbl_lining.id', 'left')
  
   ->group_by("products.id");
  
   $this->datatables->add_column("image",
   "<center>
   <a >config->base_url()."assets/uploads/$2' data-lightbox='image-1'><img >config->base_url()."assets/uploads/$2' width='100px' height='80px' title='Clique para aumentar'></a>
            </center>", "productid, image");
  
   $this->datatables->add_column("Actions",
   "<center><a id='$4 - $3' href='#'>lang->line("view_barcode")."' class='barcode tip'><i class='icon-barcode'></i></a>
  
   <a href='#' class='tip'>lang->line("product_details")."'><i class='icon-fullscreen'></i></a>
  
  
  
   <a href='index.php?module=products&view=edit&id;=$1' class='tip'>lang->line("edit_product")."'><i class='icon-edit'></i></a> <a href='index.php?module=products&view=delete&id;=$1'>lang->line('alert_x_product') ."')\" class='tip' title='".$this-&gt;lang-&gt;line("delete_product")."'><i class='icon-trash'></i></a></center>", "productid, image, code, name");
  
  $this->datatables->unset_column('productid');
  $this->datatables->unset_column('image');
    
     echo $this->datatables->generate();
  
    

   }

Heres the original code working

Code:
function getdatatableajaxcost()
   {

    $this->load->library('datatables');
    $this->datatables
  
    
   ->select("products.id as productid, products.image as image, products.code as code, products.name as name, categories.name as cname, subcategories.name as subcategory, products.cost, products.price,  products.leather as leather, products.lining as lining, products.sole as sole, COALESCE(quantity, 0) as quantity, alert_quantity, products.details", FALSE)
  
   ->from('products')
   ->join('categories', 'products.category_id=categories.id', 'left')
   ->join('subcategories', 'products.subcategory_id=subcategories.id', 'left')
  
   ->join('tbl_leather', 'products.leather=tbl_leather.id', 'left')
   ->join('tbl_lining', 'products.lining=tbl_lining.id', 'left')
   ->join('tbl_sole', 'products.sole=tbl_sole.id', 'left')
    
  
   $this->datatables->add_column("image",
   "<center>
   <a >config->base_url()."assets/upload

#2
[eluser]syrys[/eluser]
im unsure if this is correct, but worth a try anyway. I dont really know what this is:
Code:
$this->load->library('datatables')
Assuming its a database object?

Anyway, if you just create a new model, and within the model do something like:
Code:
function someFunction(){
$this->db->select('....')->from('...');
$this->db->join('....');
$this->db->join('....');
$this->db->join('....');
$this->db->join('....');

$res = $this->db->get()->result();
var_dump($res); //see if this is what you want
//if its not, do this
$q = $this->db->last_query(); //this should give you the last query, print this out and see if it built the correct sql
var_dump($q);

}

#3
[eluser]webmarkcompt[/eluser]
I updated the whole code above. Datatables is a libray http://www.datatables.net/

The difficulty here for me is to make the joins correctly...

I'm not sure I undestood your solution. This code is in controller.

#4
[eluser]syrys[/eluser]
[quote author="webmarkcompt" date="1412204929"]I updated the whole code above. Datatables is a libray http://www.datatables.net/

The difficulty here for me is to make the joins correctly...

I'm not sure I undestood your solution. This code is in controller.[/quote]
Sorry i was mistaken. I suppose this depends on the datatables library, which im not familiar with. I assumed it was a normal CI database object due to syntax similarity (maybe it is).

My code above is how you would manually get the data from the DB using a CI database object (or a model). The most useful command was the last couple of lines where it actually gets you the last run db query so you can look for the mistakes if you know your sql.

#5
[eluser]webmarkcompt[/eluser]
Thanks syrys

I am not very familiar with the codeigniter... This function is called on a index page and shows all records in database according to this query.

The query works fine when I run it on mysql.. But here I need to put the joins with this similar syntax. Such as
Code:
->join('subcatleathers sclA',
with the inner join.

The code I've tried not working, I believe the problem is in the syntax... But I do not have sufficient knowledge solve this.


#6
[eluser]webmarkcompt[/eluser]
Solved!

Thanks!

I'll be back in soon! Smile

Code:
->select("
  
   products.id as productid,
   products.image as image,
   products.code as code,
   products.name as name,
   categories.name as cname,
   subcategories.name as subcategory,
   products.cost,
   products.price,  
    
   clA.code as claCode,
            sclA.code as sclaCode,
            clB.code as clbCode,
            sclB.code as sclbCode,
            clC.code as clcCode,
            sclC.code as sclcCode,
    
   catSoleA.code as catSoleACode,
            subcatSoleA.code as subcatSoleACode,

            catSoleB.code as catSoleBCode,
            subcatSoleB.code as subcatSoleBCode,

            catSoleC.code as catSoleCCode,
            subcatSoleC.code as subcatSoleCCode,
            
   products.details", FALSE)
  
   ->from('products')
   ->join('categories', 'products.category_id=categories.id', 'left')
   ->join('subcategories', 'products.subcategory_id=subcategories.id', 'left')
  
   ->join('catleathers clA', 'products.catleather_id_a = clA.id', 'INNER')
            ->join('subcatleathers sclA', 'products.subcatleather_id_a = sclA.id', 'INNER')

            ->join('catleathers clB', 'products.catleather_id_b = clB.id', 'INNER')
            ->join('subcatleathers sclB', 'products.subcatleather_id_b = sclB.id', 'INNER')

            ->join('catleathers clC', 'products.catleather_id_c = clC.id', 'INNER')
            ->join('subcatleathers sclC', 'products.subcatleather_id_c = sclC.id', 'INNER')

            ->join('catsoles catSoleA', 'products.catsole_id_a = catSoleA.id', 'INNER')
            ->join('subcatsoles subcatSoleA', 'products.subcatsole_id_a = subcatSoleA.id', 'INNER')

            ->join('catsoles catSoleB', 'products.catsole_id_b = catSoleB.id', 'INNER')
            ->join('subcatsoles subcatSoleB', 'products.subcatsole_id_b = subcatSoleB.id', 'INNER')

            ->join('catsoles catSoleC', 'products.catsole_id_c = catSoleC.id', 'INNER')
            ->join('subcatsoles subcatSoleC', 'products.subcatsole_id_c = subcatSoleC.id', 'INNER')

->group_by("products.id");


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.