CodeIgniter Forums
Unknown column problem in datamapper - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: Unknown column problem in datamapper (/thread-32798.html)



Unknown column problem in datamapper - El Forum - 08-05-2010

[eluser]asmode[/eluser]
I am using datamapper.
I want to take count between related two tables, but i take an error like this:


Error Number: 1054
Unknown column 'table2.table2_id' in 'where clause'
SELECT COUNT(*) AS `numrows` FROM (`table2_table1`) WHERE `table2`.`table2_id` = '1' AND `table1_id` = '3'

I use examples in this page: http://stensi.com/datamapper/pages/count.html

what should i do?


Unknown column problem in datamapper - El Forum - 08-05-2010

[eluser]WanWizard[/eluser]
That is a very old and no longer maintained version of Datamapper, it might have a bug.

For an up to date version, switch to DMZ (search the forum), I just ran the example here, and it runs without problems.


Unknown column problem in datamapper - El Forum - 10-11-2010

[eluser]theprodigy[/eluser]
I have a similar error using the where_join_field function.
I am using DMZ 1.7.1 Rev. 395 (per the library header)

Receiving Error:
Quote:A Database Error Occurred

Error Number: 1054

Unknown column 'pages_stylesheets.page_id' in 'where clause'

SELECT * FROM (`stylesheets`) WHERE `pages_stylesheets`.`page_id` = 1 OR `stylesheets`.`sitewide` = '1'

My code follows:
Controller:
Code:
$this->obj = new Page();

$this->obj->get_by_name($page_name); // $page_name passed in as parameter. No issue finding page.

$this->css = new Stylesheet();
$this->js = new Javascript();

$this->css->where_join_field('pages','page_id', $this->obj->id)->or_where('sitewide', '1')->get();

Stylesheet Model:
Code:
class Stylesheet extends DataMapper
{
    var $has_many = array('page');

    var $validation = array(
        'name' => array(
            'label' => 'Name',
            'rules' => array('required', 'trim', 'max_length' => 255),
        ),
        'path' => array(
            'label' => 'Path',
            'rules' => array('required', 'trim', 'max_length' => 255),
        ),
        'sitewide' => array(
            'label' => 'Sitewide',
            'rules' => array('trim', 'callback_check_sitewide'),
        )
    );

    public function __construct($id = NULL)
    {
        parent::__construct($id);
    }

    private function check_sitewide($var)
    {
        if($var == '1')
        {
            return true;
        }

        return false;
    }
}

Javascript Model:
Code:
class Javascript extends DataMapper
{
    var $has_many = array('page');

    var $validation = array(
        'name' => array(
            'label' => 'Name',
            'rules' => array('required', 'trim', 'max_length' => 255),
        ),
        'path' => array(
            'label' => 'Path',
            'rules' => array('required', 'trim', 'max_length' => 255),
        ),
        'sitewide' => array(
            'label' => 'Sitewide',
            'rules' => array('trim', 'callback_check_sitewide'),
        )
    );

    public function __construct($id = NULL)
    {
        parent::__construct($id);
    }

    private function check_sitewide($var)
    {
        if($var == '1')
        {
            return true;
        }

        return false;
    }
}



Unknown column problem in datamapper - El Forum - 10-12-2010

[eluser]WanWizard[/eluser]
I think this is because you're not actually joining anything.

You use where_join_field() in a has_many relation where you have a join table containing extra fields besides the foreign keys. I think in this case you need to use where_related().


Unknown column problem in datamapper - El Forum - 10-12-2010

[eluser]theprodigy[/eluser]
Thanks, that did the trick!
Now I'm running into a slightly different mis-happening (not an error, just unexpected results).

When dumping $this->css->to_array(), it is showing only one element, but when dumping $this->db->last_query(), and copying and pasting the resulting query into phpMyAdmin, it is showing 3 results.

What I'm wanting is all stylesheets where page_id = the page id of the current page or where stylesheets.sitewide = 1.

Code:
Code:
$this->obj = new Page();

$this->obj->get_by_name($page_name);

$this->css = new Stylesheet();
$this->js = new Javascript();

$this->css->where_related($this->obj)->or_where('sitewide', '1')->get();

SQL Dumps:
pages:
Code:
CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `name` varchar(255) NOT NULL,
  `meta_keywords` varchar(255) NOT NULL,
  `meta_description` text NOT NULL,
  `title` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `pages`
--

INSERT INTO `pages` (`id`, `name`, `meta_keywords`, `meta_description`, `title`, `created_at`, `updated_at`) VALUES
(1, 'home', 'test, test_page, page_test', 'This is a test page for the page library.', 'Test Page', '2010-10-05 00:02:00', '2010-10-05 00:02:00'),
(2, 'about', 'about, about_page, page_about', 'This is to test that it doesn''t pull this page when the home page is pulled', 'About Page', '2010-10-05 00:02:00', '2010-10-05 00:02:00');
stylesheets:
Code:
CREATE TABLE IF NOT EXISTS `stylesheets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `name` varchar(255) NOT NULL COMMENT 'human readable name',
  `path` varchar(255) NOT NULL COMMENT 'relative, absolute, or url',
  `sitewide` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'put on all pages?',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `stylesheets`
--

INSERT INTO `stylesheets` (`id`, `name`, `path`, `sitewide`, `created_at`, `updated_at`) VALUES
(1, 'Test css', 'test.css', 1, '2010-10-05 00:01:35', '2010-10-05 00:01:40'),
(2, 'Non sitewide test', 'non_sw_test.css', 0, '2010-10-05 00:02:00', '2010-10-05 00:02:00'),
(3, 'Home_non_sitewide', 'home_non_sw.css', 0, '2010-10-05 00:02:00', '2010-10-05 00:02:00');
pages_stylesheets:
Code:
CREATE TABLE IF NOT EXISTS `pages_stylesheets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `page_id` int(11) unsigned DEFAULT NULL,
  `stylesheet_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `page_id` (`page_id`),
  KEY `stylesheet_id` (`stylesheet_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `pages_stylesheets`
--

INSERT INTO `pages_stylesheets` (`id`, `page_id`, `stylesheet_id`) VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 1);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `pages_stylesheets`
--
ALTER TABLE `pages_stylesheets`
  ADD CONSTRAINT `pages_stylesheets_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `pages_stylesheets_ibfk_4` FOREIGN KEY (`stylesheet_id`) REFERENCES `stylesheets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;



Unknown column problem in datamapper - El Forum - 10-12-2010

[eluser]WanWizard[/eluser]
The to_array() method only returns the currrently selected record in the resultset. If you want an array of the entire resultset, use all_to_array().


Unknown column problem in datamapper - El Forum - 10-12-2010

[eluser]theprodigy[/eluser]
Thank you. That solved it.