[Swede]

Hi,

I am using set_relation 1-n in combination with 'Where' and URL parameter.
Can't get it to work properly.

I had no problem with the set_relation until I was strting to use 'Where'.

When running this procedure I get the filtered result, but only for half a second, then the list is rebuilt, with no list records.

	public function ny_nbc_match()

	{

		$crud = new grocery_crud();

		$id = $_GET['lag'];

		$crud->set_subject('match');

		$crud->set_table('ny_nbc_match');

		$crud->order_by('ny_nbc_match.ny_nbc_match_date','asc');

		$crud->where('ny_nbc_match.ny_nbc_match_lag_id',$id);

		$crud->display_as('ny_nbc_match_date','Matchdatum');

		$crud->display_as('ny_nbc_match_season_id','Säsong');

		$crud->display_as('ny_nbc_match_hall_id','Hall');

		$crud->display_as('ny_nbc_match_lag_id','Lag');

		$crud->required_fields('ny_nbc_match_date','ny_nbc_match_season_id','ny_nbc_match_lag_id','ny_nbc_match_hall_id');

		$crud->set_relation('ny_nbc_match_lag_id','ny_nbc_lag','ny_nbc_lag_namn',null,'ny_nbc_lag_namn ASC');

		$crud->set_relation('ny_nbc_match_season_id','ny_nbc_season','ny_nbc_season_namn',null,'ny_nbc_season_namn DESC');

		$crud->set_relation('ny_nbc_match_hall_id','ny_nbc_hallar','ny_nbc_hallar_namn',null,'ny_nbc_hallar_namn ASC');

		$output = $crud->render();



		$this->_example_output($output);

	}

If I hardcode the filter value in Where, the list is shown, filtered according to the hardcoded value.

$crud->where('ny_nbc_match.ny_nbc_match_lag_id',2);

If I keep the $id variable in the where-clause and disable the set_relation on ny_nbc_lag, the filter works, no "auto-refresh" that removes the list after a half second. But, of course without any relation to that table that I need

$crud->where('ny_nbc_match.ny_nbc_match_lag_id',$id);

...

// $crud->set_relation('ny_nbc_match_lag_id','ny_nbc_lag','ny_nbc_lag_namn',null,'ny_nbc_lag_namn ASC');

I realize that I have to do some filtering (where) in the set_relation as well. But I don't know how. I have tried to add this filter in the set_relation but without any affect on the result.

Other thing about this, the sort order on the "set_relation('ny_nbc_match_lag_id','ny_nbc_lag','ny_nbc_lag_namn',null,'ny_nbc_lag_namn ASC');"
seems to override the sort order "$crud->order_by('ny_nbc_match.ny_nbc_match_date','asc');" in the list. Why?

Appreciate some help here

[noskov.biz]

Hi, Swede!
For me $crud->where() works well with the default example database. I have a question: what You have got in your $id = $GET['lag'] ? Is there any errors?

[quote][color=#282828][font=helvetica, arial, sans-serif]I realize that I have to do some filtering (where) in the set_relation as well.[/font][/color][/quote]
You can use where clause in the set_relation function as the 4-th parameter. For example,

$crud->set_relation('ny_nbc_match_lag_id', 'ny_nbc_lag', 'ny_nbc_lag_namn', $you_where_clause, 'ny_nbc_lag_namn ASC');

Sorry, if I do not understand what exactly you want to do/

[Swede]

Thanks for the reply [color="#225985"]noskov.biz[/color],

Yes, $crud->where() works well for me too, but not in the combination of set_relation.
Not in my project anyway, .

$GET['lag'] will retrieve a integer, that will be used as a filter to my list records.
As you can see in my examples, this works in some circumstances, and that confuses me.

Lag = Team in swedish, Namn = Name
So... My intention is to lookup the team name for the list and for the Add/Update views, to have a pre-filled combo with values from according tables.
Works fine with the other two set_relation but not with the ny_nbc_lag relation if I combine it with a variable ($id, or $_GET['lag]

Hope I clearified some...

[noskov.biz]

Yes, I have understood, thanks!

Hm Tell me, please, what versions of codeigniter and grocery crud do you use?

[Swede]

codeigniter 2.1.1
grocery crud 1.2.3

[Swede]

Sql for the database and some test data

-- phpMyAdmin SQL Dump

-- version 2.11.8.1deb5+lenny9

-- http://www.phpmyadmin.net

--

-- Skapad: 06 juli 2012 kl 21:25

-- Serverversion: 5.1.49

-- PHP-version: 5.3.3-7+squeeze13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";



/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--



--

-- --------------------------------------------------------

--

-- Struktur för tabell `ny_nbc_hallar`

--

CREATE TABLE IF NOT EXISTS `ny_nbc_hallar` (

  `ny_nbc_hallar_id` int(11) NOT NULL AUTO_INCREMENT,

  `ny_nbc_hallar_namn` varchar(50) NOT NULL,

  `ny_nbc_hallar_hemma` tinyint(1) NOT NULL,

  PRIMARY KEY (`ny_nbc_hallar_id`),

  UNIQUE KEY `ny_nbc_hallar01` (`ny_nbc_hallar_namn`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Data i tabell `ny_nbc_hallar`

--

INSERT INTO `ny_nbc_hallar` (`ny_nbc_hallar_id`, `ny_nbc_hallar_namn`, `ny_nbc_hallar_hemma`) VALUES

(1, 'NBC Gullmarsplan', 1),

(2, 'Ã…keshov', 0),

(3, 'Mälarhallen', 0);

-- --------------------------------------------------------

--

-- Struktur för tabell `ny_nbc_lag`

--

CREATE TABLE IF NOT EXISTS `ny_nbc_lag` (

  `ny_nbc_lag_id` int(11) NOT NULL AUTO_INCREMENT,

  `ny_nbc_lag_namn` varchar(50) NOT NULL,

  `ny_nbc_lag_uk` tinyint(1) NOT NULL DEFAULT '0',

  `ny_nbc_lag_antalspelare` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ny_nbc_lag_id`),

  UNIQUE KEY `ny_nbc_lag01` (`ny_nbc_lag_namn`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Data i tabell `ny_nbc_lag`

--

INSERT INTO `ny_nbc_lag` (`ny_nbc_lag_id`, `ny_nbc_lag_namn`, `ny_nbc_lag_uk`, `ny_nbc_lag_antalspelare`) VALUES

(1, 'NBC BC A', 1, 8),

(2, 'NBC BC B', 0, 8),

(3, 'NBC BC C', 0, 4);

-- --------------------------------------------------------

--

-- Struktur för tabell `ny_nbc_match`

--

CREATE TABLE IF NOT EXISTS `ny_nbc_match` (

  `ny_nbc_match_id` int(11) NOT NULL AUTO_INCREMENT,

  `ny_nbc_match_date` date NOT NULL,

  `ny_nbc_match_season_id` int(11) NOT NULL,

  `ny_nbc_match_hall_id` int(11) NOT NULL,

  `ny_nbc_match_lag_id` int(11) NOT NULL,

  PRIMARY KEY (`ny_nbc_match_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--

-- Data i tabell `ny_nbc_match`

--

INSERT INTO `ny_nbc_match` (`ny_nbc_match_id`, `ny_nbc_match_date`, `ny_nbc_match_season_id`, `ny_nbc_match_hall_id`, `ny_nbc_match_lag_id`) VALUES

(1, '2012-07-03', 1, 1, 2),

(2, '2012-07-25', 1, 1, 3),

(3, '2012-07-17', 1, 2, 3),

(4, '2012-07-17', 1, 1, 1),

(5, '2012-07-18', 1, 1, 2),

(6, '2012-06-03', 1, 1, 2),

(7, '2012-05-07', 3, 2, 2),

(8, '2012-07-01', 3, 1, 2),

(9, '2012-07-10', 3, 1, 2),

(10, '2012-07-09', 3, 2, 3);

-- --------------------------------------------------------

--

-- Struktur för tabell `ny_nbc_season`

--

CREATE TABLE IF NOT EXISTS `ny_nbc_season` (

  `ny_nbc_season_id` int(11) NOT NULL AUTO_INCREMENT,

  `ny_nbc_season_namn` varchar(50) NOT NULL,

  PRIMARY KEY (`ny_nbc_season_id`),

  UNIQUE KEY `ny_nbc_season_namn` (`ny_nbc_season_namn`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Data i tabell `ny_nbc_season`

--

INSERT INTO `ny_nbc_season` (`ny_nbc_season_id`, `ny_nbc_season_namn`) VALUES

(1, '2012/2013'),

(2, '2009/2010'),

(3, '2010/2011');

-- --------------------------------------------------------

--

-- Struktur för tabell `ny_nbc_spelare`

--

CREATE TABLE IF NOT EXISTS `ny_nbc_spelare` (

  `ny_nbc_spelare_id` int(11) NOT NULL AUTO_INCREMENT,

  `ny_nbc_spelare_fornamn` varchar(30) NOT NULL,

  `ny_nbc_spelare_efternamn` varchar(30) NOT NULL,

  `ny_nbc_spelare_user` char(20) NOT NULL,

  `ny_nbc_spelare_passw` char(20) NOT NULL,

  PRIMARY KEY (`ny_nbc_spelare_id`),

  UNIQUE KEY `ny_nbc_spelare01` (`ny_nbc_spelare_fornamn`,`ny_nbc_spelare_efternamn`),

  KEY `ny_nbc_spelare02` (`ny_nbc_spelare_user`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

--

-- Data i tabell `ny_nbc_spelare`

--

INSERT INTO `ny_nbc_spelare` (`ny_nbc_spelare_id`, `ny_nbc_spelare_fornamn`, `ny_nbc_spelare_efternamn`, `ny_nbc_spelare_user`, `ny_nbc_spelare_passw`) VALUES

(18, 'dfdf', 'sss', 'dfdfdsfsdf', ''),

(11, 'OPOPOP', 'dddddddddddddddddddddddddesff', '', ''),

(13, 'sertertet', 'uiuiooxxxxxxxxxxx', '', ''),

(15, 'Bosse', 'sefsf', '', ''),

(16, 'UYYTT', 'GHGJHGG', '', ''),

(17, 'dddddddddddddddd', 'dddd', '', '');

[noskov.biz]

Hi!

I have played a little bit with you data. And make a fresh instalation of CodeIgniter-[b]2.1.2[/b] and the [b]latest version[/b] of grocery CRUD from Github. Here it is your function ny_nbc_match() with few changes. It works at my local machine well:

[attachment=216:for_swede.png]

public function ny_nbc_match()

{

//$id = $_GET['lag'];

$lag = $this->db->select('ny_nbc_lag_id')->get_where('ny_nbc_lag', array('ny_nbc_lag_namn' => 'NBC BC C'))->row();

//print_r($lag->ny_nbc_lag_id); exit();



$crud = new grocery_crud();



$crud->set_table('ny_nbc_match');

$crud->set_subject('match');

 

$crud->display_as('ny_nbc_match_date','Matchdatum');

$crud->display_as('ny_nbc_match_season_id','Säsong');

$crud->display_as('ny_nbc_match_hall_id','Hall');

$crud->display_as('ny_nbc_match_lag_id','Lag');



$crud->required_fields('ny_nbc_match_date', 'ny_nbc_match_season_id', 'ny_nbc_match_lag_id', 'ny_nbc_match_hall_id');

 

$crud->order_by('ny_nbc_match.ny_nbc_match_date', 'asc');

$crud->where('ny_nbc_match.ny_nbc_match_lag_id', $lag->ny_nbc_lag_id);



$crud->set_relation('ny_nbc_match_lag_id', 'ny_nbc_lag', 'ny_nbc_lag_namn', array('ny_nbc_lag_id' => $lag->ny_nbc_lag_id), 'ny_nbc_lag_namn ASC');

 

$crud->set_relation('ny_nbc_match_season_id', 'ny_nbc_season', 'ny_nbc_season_namn', null, 'ny_nbc_season_namn DESC');

$crud->set_relation('ny_nbc_match_hall_id', 'ny_nbc_hallar', 'ny_nbc_hallar_namn', null, 'ny_nbc_hallar_namn ASC');



$output = $crud->render();

$this->_example_output($output);

}

Is it works as expected for you?

P.S. It's just my opinion: but I had some strange behaviour of CI-2.1.1 even without Grocery. So, please, try it with the latest Grocery and CI-2.1.0 or CI-2.1.2. If you want I can send you my app with your function.

[Swede]

Thank you for your help [color="#225985"]noskov.biz[/color], It works now with the Where in combination with the Set_relation.

Ii also upgraded to CI-2.1.2.

I think that URL parameters was not so good idea to use in grocery CRUD/CI.
To get the funcionality to select specific records, I will try to use session parameters instead.

Thanks for taking time to help me...

[noskov.biz]

You are welcome! I'm glad that everything works well for you now

[noskov.biz]

By the way. If you want to make your urls more informative, you can pass the name of team as the 1-st parameter of your function:

public function ny_nbc_match($current_url)

{

$lag = $this->db->select('ny_nbc_lag_id, ny_nbc_lag_namn')->get_where('ny_nbc_lag', array('ny_nbc_lag_namn' => 'NBC BC C'))->row();

$current_lag = $lag->ny_nbc_lag_namn;

...

Grocery CRUD perfectly works with uri segments and will understand what you want to do with the data in your current team section. There is just a hint and it is pretty raw. You have to do with you $current_lag some stuff in order to prepare $lag->ny_nbc_lag_namn to be uri segment.