Thread

Thank you Phil, But sorry for my English is very pour and I don’t know how I can explain what I am trying to pull…
In your examples there is user and group models and their relations.

my region and city is similar like user to group but I have to get data

all regions and the regions cities lets say I have 3 region and 20 city, region 1 has 10 city, region 2 does not have any, 3 region has 10.

I want to list all this data…

Thanks for your work & helps…

Ps : I am still reading all discuss from the begging to learn DMZ and User guide is allways open in my browser.

@BaRzO

I think I see the issue: my first example doesn’t include regions with no cities.

You probably will have to do it the old-fashioned way:

$regions = new Region();
$regions->get();
foreach($regions as $region) {
    // here's a region
    $region->city->get();
    $number_of_cities = count($region->city->all);
    foreach($region->city as $city) {
        // here's a city
    }
}

(That creates more queries, of course.)

There’s no practical way with DMZ to combine a $has_many relationship into one query. 😊

(Well, that’s not 100% true.  I have a trick you can use on PostgreSQL to get the results of $has_many relationships, but it’s kinda hacky, and, again, only works on PostgreSQL.)

This one works well 😊 as you said this one creates more query,
I will try onther way like user to group not group to user…

Thanks for youe help… I have to read more 😉

Ps. zestyjobs.com is really god work…

@BaRzO

I thought of a trick to get it down to 2 queries! Combine both techniques:

$regions = new Region();
$regions->order_by('name')->get();

$city = new City();

$city->include_related('region', array('id', 'name', ...));
$city->order_by_related('region', 'name', 'ASC'); // order by region first
$city->order_by('name', 'ASC'); // then by city name
$city->get();

$city_index = 0;
$total_cities = count($city->all);

foreach($regions as $r) {
    echo($c->region_name);
    while(($city_index < $total_cities) &&
            ($city->all[$city_index]->region_name == $r->name)) {
        $c = $city->all[$city_index];
        // output city info
        echo($c->name);
        $city_index++;
    }
}

At your last code I get only one region, I get correct cities but only one region coming up.
I get this query…

SELECT * FROM `regions` LIMIT 1 
SELECT * FROM (`regions`) ORDER BY `regions`.`name` 
SELECT * FROM `cities` LIMIT 1
SELECT `cities`.*, `regions`.`name` AS region_name, `regions`.`status` AS region_status
FROM (`cities`)
LEFT OUTER JOIN `regions` as regions ON `regions`.`id` = `cities`.`region_id`
ORDER BY `regions`.`name` ASC, `cities`.`name` ASC

@BaRzO

I really can’t see where something would have gone wrong.  Try just looping through the regions, and seeing if there is more than one.

I didn’t put any linebreaks in, so you might not be seeing the regions?

I would try running those queries by hand in the database.  You may find that you have every city assigned to the same region, OR you are only getting one region.

Are you sure your database has the right data?

I have attached an image of tables…

city model

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class City extends DataMapper {

    var $table = 'cities';

    //  region has many
    var $has_one = array('country', 'region');

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

region model

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Region extends DataMapper {
    
    var $table = 'regions';
    
    //  region has many
    var $has_many = array('city');
    
    function __construct($id = NULL) {
        parent::__construct($id);
    }
}

foreach($regions as $r) {
    echo($c->region_name);

should be

foreach($regions as $r) {
    echo($r->name);

@Conerck
LOL - That might be the problem!  😛

That is, why does DMZ require the use of an extra joining table even when there is just a one-to-many relationship between two tables? What’s the problem with the traditional approach of having a foreign key in the table of ‘many’ and making it refer to the primary key in the table of ‘one’?

As mentioned earlier, it does not require an extra table.  DMZ handles In-Table Foreign Keys, which it says on the first page of the manual 😉, as well as under Database Tables (see the bottom).

DMZ is based upon the older DataMapper by stensi, and his design required full Fifth-normal form compliance.  Obviously that has negative performance and database complexity side effects, which was one of the original factors for forking DataMapper.  (Well, is it really a fork if the original has not been updated?)

There is an awesome new release coming soon (probably early next week), but the changes are mostly performance and new-features based, so the upgrade from 1.6.2 to 1.7.0 should be very smooth.

Yes, I am sorry I must have read the documentation of the original version of DataMapper and thought it’s your version. I am now feeling very safe to use your DMZ.

Thank you very very much for your wonderful ORM.

It doesnt require a join table… just for n to n relations

Thank you very much for pointing this out. I made a mistake. DMZ does not require a joining table for a 1:N relationship while it seems to be the case for the original version of DataMapper.

@Conerck
When I use with $c->region_name or $c->name it does not work.
I have changed it like $city->region_name It’s works but get only one region.
Is it possible I don’t know, the problem is the sql query maybe there is a LIMIT 1 for region

$regions = new Region();
        $regions->order_by('name')->get();

        $city = new City();

        $city->include_related('region', array('id', 'name', 'status'));
        $city->order_by_related('region', 'name', 'ASC'); // order by region first
        $city->order_by('name', 'ASC'); // then by city name
        $city->get();

        $city_index = 0;
        $total_cities = count($city->all);

        foreach($regions->all as $r) {
            echo('<span>'.$city->region_name.'
</span>');
            while(($city_index < $total_cities) &&
                    ($city->all[$city_index]->region_name == $r->name)) {
                $c = $city->all[$city_index];
                // output city info
                echo('<span>'.$c->name.', </span>');
                $city_index++;
            }
            echo '<hr>';
        }

@Conerck
When I use with $c->region_name or $c->name it does not work.
I have changed it like $city->region_name It’s works but get only one region.
Is it possible I don’t know, the problem is the sql query maybe there is a LIMIT 1 for region

It should be
$r->name

The $r variable holds the Region data. The $c variable holds the City data.

The LIMIT 1 queries are not the problem. They are made by DMZ to find out what fields are in your tables.

EDIT: You should also take a look at the Model-View-Controller-Pattern

Thanks you are very careful. :red:

@BaRzO, Conerck

I feel this is my fault.  I need to stop that bad habit of using single-letter variables in example code!  (I never would do it in production, so why do it in examples?)  😛

Have a good weekend, everyone.  I’ll probably be polishing 1.7 this weekend, and I’ll put out a beta release for a little while first, so everyone can see if I missed anything.