Thread

@Mark,
Thanks for your help. Anyway this alias in not working for me even with the most simple piece of code:

<?php

class Posts_model extends MY_Model
{

    function __construct()
    {
        parent::__construct();

        $this->load_table('posts');
        $this->load_table('links', 'urls');

        $this->posts->related('links', array('link', 'id'));
    }

    public function count_links($box)
    {
        return $this->with("urls")->count(array('name' => $box));
    }

The result for this is:

A Database Error Occurred
Error Number: 1146

Table ‘db_name_here.urls’ doesn’t exist

I guess I am doing something generaly wrong then :/

You need to refer to the alias in subsequent code. Also try using “$this->urls” in the with().

class Posts_model extends MY_Model
{

    function __construct()
    {
        parent::__construct();

        $this->load_table('posts');
        $this->load_table('links', 'urls');
        
        // now we refer to the links table by it's alias 'urls'
        $this->posts->related('urls', array('link', 'id'));
    }

    public function count_links($box)
    {
        return $this->with("$this->urls")->count(array('name' => $box));
    }

Possible bug in the load_table function:

The function is as follows:

protected function load_table($table, $alias='', $fields=array(), $pk='id')
{
    $alias                        = !empty($alias) ? $alias : $table;
    $this->$alias                 = new Table($table, $alias);
    $this->$alias->fields         = (!empty($fields)) ? $fields : $this->db->list_fields($table);
    $this->$alias->pk             = (!empty($pk)) ? $pk : $this->$alias->fields[0];
        
    // make the first table loaded the default primary table for this model
    if ($this->_table == null)
    {
        $this->_table = $alias; 
    }
}

The declaration provides a default value for the primary key ($pk).

However, the function is designed to use the first column as the primary key if $pk is blank. I submit that the default for $pk should not be ‘id’, but rather be blank.

As it is, since I don’t use ‘id’ as my primary key, I’m finding that I need to do this:

$this->load_table('affiliates','','','');
$this->load_table('affiliates_commissions','','','');
$this->load_table('affiliates_product_pricing','','','');
$this->load_table('affiliates_tracking','','','');

I could remove all of those superfluous quotes from these statements if the default $pk=‘id’ is changed to $pk=’’ in the function declaration.

Thoughts?

I agree with you. I’ll take it out for the next release.

EDIT: updated to 1.0.4

@Mark, please help me again ... I have a simple query, which depends on if the category is selected or not (not means all posts are displayed in desc order, but if we have a category selected, then we are displaying only posts from this category).
$this->load_table(‘posts’);<br /> $this->load_table(‘categories’);</p> <p>$this->posts->related(‘categories’, array(‘category’, ‘id’));</p> <p>// Code ...</p> <p>($category) ? $this->where(array(‘name’ => $category)) : ‘’;<br /> // Now the problem begins:<br /> $this->with(‘categories’) // Tried with this, or without ... Should I use it when I already described the tables relations?<br />   ->select(‘id, title, body, categories.name, image, published’) // Should I specify here that `names` are from categories table?<br />   ->from() // Empty? `posts` is my first (default) table ...<br />   ->order_by(‘posts.id’, ‘desc’) // Should I use ‘posts.id’ or just ‘id’ here? Both tables have id field ...<br />   ->limit($limit, $offset);</p> <p>return $this->get();</p> <p>// Code ...

I was trying everything ... no success ... Using the normal CI active record methods, I am able to do it with join. I tried using join here also. No success :(

Any ideas?

Lots of ways to do this! I’ve tried to explain in the comments what’s going on:

// in constructor
$this->load_table('posts');
$this->load_table('categories');
$this->posts->related('categories', array('category', 'id'));

// as 'posts' is the first table loaded the model is 'pointing' to the posts table.
// we could either use with() to point to the categories table, and then with() again to go back to posts
// or we can just pass a second parameter to where() explicitly stating the table it applies to:
if ($category) $this->where(array('name' => $category), 'categories');

// model is still pointing to posts, all we need to do is pass get() a list of fields to get
// And as 'categories' table has been used by where() above, the table will be joined to 'posts' automatically
return $this->get(array(
    'fields'   => array('id', 'title', 'body', 'categories.name', 'image', 'published'),
    'order_by' => 'id',
    'sort'     => 'desc',
    'limit'    => $limit,
    'offset'   => $offset
    );

// this would also work if 'name' is not a column in the 'posts' table
// because get will simply look at the related table for the column name:
return $this->get(array(
    'fields' => array('id', 'title', 'body', 'name', 'image', 'published')
    );

Lots of ways to do this! I’ve tried to explain in the comments what’s going on:
...

Thanks but it is not working as expected… Will explain:
If I am at site.com/blog
everything is Ok (there is no category selected, displaying all the posts, paginated). If I go to a category, like site.com/category/other/
then the problem exists.
This is the example code:
//The model code:</p> <p>if ($category) $this->where(array(‘name’ => $category), ‘categories’);</p> <p>return $this->get(<br />   array(<br />     ‘fields’  => array(‘id’, ‘title’, ‘body’, ‘name’, ‘image’, ‘published’),<br />     ‘order_by’ => ‘id’,<br />     ‘sort’    => ‘desc’,<br />     ‘limit’  => $limit,<br />     ‘offset’  => $offset<br />   )<br />   );</p> <p><br /> //The controller code:</p> <p>public function index($offset = NULL)<br /> {<br /> $result = $this->blog->get_posts($limit, $offset);</p> <p> if ($result)<br /> {<br /> foreach ($result as $row)<br /> {<br /> $posts[] = array(<br /> 'title' => $row['title'],<br /> 'body' => $row['body'],<br /> 'category' => $row['name'],<br /> // ... and others ...<br /> );<br /> }<br />   }<br /> }</p> <p>// print_r($result); -> this gives me here: Array([0] => Array([id] => 7 [title] => Sample post [body] => Lorem ipsum dolor sit amet ... [name] => other [published] => 2010 ... and so on ...))<br /> // this is correct ...</p> <p>public function category($category = NULL, $offset = NULL)<br /> {<br /> $category = $this->blog->check_category($category);<br /> $result = $this->blog->get_posts($limit, $offset, $category);</p> <p> if ($result)<br /> {<br /> foreach ($result as $row)<br /> {<br /> $posts[] = array(<br /> 'title' => $row['title'],<br /> 'body' => $row['body'],<br /> 'category' => $row['name'],<br /> // ... and others ...<br /> );<br /> }<br />   }<br /> }</p> <p>// print_r($result); -> this gives me here Array([0] => Array([id] => 2 [name] => other))<br /> // this is totally wrong ...
The result is:

A PHP Error was encountered

Severity: Notice

Message: Undefined index: title

Any ideas? :(

Quick update… If I change the code of the funtion get_posts like this:
//The model code:</p> <p>if ($category) $this->where(array(‘name’ => $category));</p> <p>return $this->get(<br />   array(<br />     ‘fields’  => array(‘id’, ‘title’, ‘body’, ‘name’, ‘image’, ‘published’),<br />     ‘order_by’ => ‘id’,<br />     ‘sort’    => ‘desc’,<br />     ‘limit’  => $limit,<br />     ‘offset’  => $offset<br />   ), ‘posts’ // Adding ‘posts’ here as the table where we are working ...<br />   );
Now it works, but there is a new problem ... The pagination in a selected category is simply missing in this case :( Which means, it is showing only the last posts (10 for example, according to the specified offset in the controller).
There is something strange in changing the primary tables I think :/

Looks like you need to make sure that your query is pointing to the right table when it gets the requested fields.

$options = array(
       'fields'   => array('id', 'title', 'body', 'name', 'image', 'published'),
       'order_by' => 'id',
       'sort'     => 'desc',
       'limit'    => $limit,
       'offset'   => $offset
        );

// assuming your categories table is called 'categories'
if ($category) $options += array('categories.name' => $category);

return $this->with('posts')->get($options);

Thanks, @Mark! I’ve did it somehow… These changes of the primary table are still too hard to understand. But now it works. Here is my new question… Please, can you give an example query for a full text search?
I am having a table, let’s say `links`. And two columns are indexed. But in the normal way with “where”, I have to do like:
$this->where(array(‘field’ => $variable))
How can I do it with MATCH AGAINST? Like:
$this->from() // If there is no from(), MySQL error occurs, have no idea why<br />     ->where(‘here is the MATCH AGAINST thing…’)<br />     ->get();
Yes, this works actually, but I have no idea why it needs “from()” and in this case I can not use your magic joins :/
And I need one join here, actually 😊

Primary table - if in doubt, use with(‘my_table’) at the start of your query. What you are not appreciating is that preceding queries may have moved the ‘pointer’ to another table. The query is not automatically initialized with each model function. This is by design.

For your join you can do join(‘my_table’ [, ‘join type’]), or if you have referenced a column in a related table in your where() condition, simply call join() with no arguments to do a natural left join.

If you call get() with no arguments this is equivalent to CI’s Active Record $this->db->get(), but with the difference that a result array is returned rather than the query object. Hence from() is required, just as it would be with Active Record.

Thanks, @Mark! But ... My main question was about where and full text search with MATCH AGAINST. Can you give me an example about this? My primary table is `links` and I just need a simple join with the table `categories`, where the name of the category is.
So actually in the `links` table I have a column `category`, where the category id is inserted. I just need this join to get the actual name of the category. So if I have in `links` category id = 3, I want to have in the result array ... category name like ‘design’, which is from the `categories` table ...
But anyway, the main question is about using where with full text.

It’s the same as AR except as before you should prepend the table alias for the column:

$this->where('MATCH (my_table_alias.field) AGAINST ("value")');

Hi Mark

I’m just starting to use your model and think it looks good.

I’m just wondering if it is possible to do a get to retrieve all fields from the primary and related tables without having to specify all field names individually?

Thank you

Hi Mark

I’ve modified your join function so I can related tables using more than one column.

Setup the relation

$this->node->related('node_revisions', array('nid' => 'nid', 'vid' => 'vid'));

Call join as part of the query - this is the standard way

$this->join('node_revisions', 'left', true);

New code is inside the “if (array_key_exists($table2, $related))”

/**
     * Join two tables
     *
     * @param string  $table the table to be joined
     * @param string  $type  the join type
     * @param boolean $with  use the joined table as the primary table for this model?
     * @return object (success), bool (failure)
     */
    protected function join($table=null, $type='left', $with=false)
    {
        if (is_null($table))
        {
            if (count($this->tables_used) > 0)
            {    
                foreach($this->tables_used as $table)
                {
                    // join table (recursive)
                    $this->join($table, $type);
                }
                // reset the used table array
                $this->tables_used = array();
                
                return $this;
            } 
            else return false;
        }
        else if (!in_array($table, $this->tables_joined))
        {
                
            $table1 = $this->_table;
            $table2 = $table;
        
            $related = $this->$table1->related();

            if (array_key_exists($table2, $related))
            {    
                // get the keys as specified in the relationship
                if(empty($related[$table][0])) {
                    $join_on = '';
                    foreach($related[$table] as $fk => $pk) {
                        // Work out default key values if keys are empty
                        // Assumptions: foreign key column is named after the concatenation of the
                        // singular of the joined table, underspace '_', and the joined table's primary key.
                        // Eg table 1 with column: office.country_iso ; table 2 with primary key: countries.iso
                        if ($fk=='') $fk = singular($this->$table2->table).'_'.$this->$table2->pk;
                        if ($pk=='') $pk = $this->$table2->pk;

                        if(!empty($join_on)) {
                            $join_on .= ' AND ';
                        }

                        $join_on .= $this->$table1->alias.'.'.$fk.' = '.$this->$table2->alias.'.'.$pk;
                    }
                    if(!empty($join_on)) {
                        // do the join
                        $this->db_conn->join(
                            $this->$table2->table.' '.$this->$table2->alias,
                            $join_on,
                            $type
                        );
                    }
                }
                else {
                    $fk = $related[$table2][0];
                    $pk = $related[$table2][1];

                    // Work out default key values if keys are empty
                    // Assumptions: foreign key column is named after the concatenation of the
                    // singular of the joined table, underspace '_', and the joined table's primary key.
                    // Eg table 1 with column: office.country_iso ; table 2 with primary key: countries.iso
                    if ($fk=='') $fk = singular($this->$table2->table).'_'.$this->$table2->pk;
                    if ($pk=='') $pk = $this->$table2->pk;

                    // do the join
                    $this->db_conn->join(
                        $this->$table2->table.' '.$this->$table2->alias,
                        $this->$table1->alias.'.'.$fk.' = '.$this->$table2->alias.'.'.$pk,
                        $type
                    );
    
                }
                // add to array of joined tables
                $this->tables_joined[] = $table2;

                // set joined table as primary table for this model (note: do not inititalise)
                if ($with) $this->with($table2, false);

                return $this;
            }
    
            else return false;
        }
        else
        {
            // attempt to join an already joined table - fail gracefully
            return $this;
        }    
    }

The only thing is it produces sql like this

JOIN `node_revisions` node_revisions ON `node`.`nid` = `node_revisions`.`nid` AND node.vid = node_revisions.vid

where the second and subsequent columns in the join are not protected by the Active Record join function :-(