[El Forum]

[eluser]heldrida[/eluser]
Hi,
for the following scheme:

---------------------------------------------
table -> Campaigns
id | name | alias | start_date | end_date

---------------------------------------------
table -> Campaigns_products
id | campaign_id | product_id

---------------------------------------------
table -> Products
id | reference | name | description

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

table -> Categories
id | name | parent_category_id
---------------------------------------------

table -> Products_categories
id | product_id | category_id

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

- A product can have many categories;
- A product can have many Campaigns;

My question is, I dont know how to get a product, that can be in many
categories, in a specific campaign:

- The product must be in catA or catB or catC or catD;
- AND it must be of campaignX;

Here's some code http://pastie.org/2316484

I can do this by writting SQL, but I would like to understand how I can accomplish
this trough Datamapper.

Code:

The lines 59 to 70

                foreach ( $cat As $k => $v )
                {                
                    if ( $i == 0 )
                    {
                        $prod->where_related($v);
                    }
                    else
                    {
                        $prod->or_where_related($v);
                    }
                $i++;
                }


The lines 80 to 88

            $cpgn = new Campaign();
            $cpgn->where('id', $cpgn_id)->get();
            
            if ( ! $cpgn->exists() )
            {
                return FALSE;
            }

            $prod->where_related($cpgn)->get();

On $prod->where_related($cpgn)->get(), I'm getting a product that is campainX, that is not part of the selected category.

Also, is there a way to see the query it generated ? What I do often is to place typos to get the error msgs with the query. From the last querys I saw, I guess I could solve this if I had the "AND_WHERE" or something :T

Code:

the following query works fine:

SELECT * FROM `products`
INNER JOIN `products_categories`
ON `products_categories`.`product_id` = `products_categories`.`category_id`
INNER JOIN `campaigns_products`
ON `campaigns_products`.`product_id` = `products`.`id`
INNER JOIN `campaigns`
ON `campaigns`.`id` = `campaigns_products`.`campaign_id`
WHERE (
`products_categories`.`category_id` = 1
OR `products_categories`.`category_id` = 3
OR `products_categories`.`category_id` = 4
OR `products_categories`.`category_id` = 5
)
AND `campaigns`.`id` = 1

Thanks a lot for looking!

[El Forum]

[eluser]WanWizard[/eluser]
You can dump the SQL by using $yourobject->check_last_query().

And I suspect you have an issue with where order here, as you correctly stated, the categories need to be within brackets. See http://datamapper.wanwizard.eu/pages/get...y.Grouping on how to do that.

And my I suggest you upgrade your Datamapper? v1.6.0 is ancient. And if you're not using this version, you're posting in the wrong thread.

[El Forum]

[eluser]heldrida[/eluser]
Thanks a lot for looking and taking tour time, I really appreciate it!
I dont know if I should copy and paste to the new thread, but I guess I'll do that.

I tought I've checked all the document, didnt noticed the query.grouping :X I'll test
it!

[El Forum]

[eluser]amityweb[/eluser]
HI

On your site you have examples for Save. I have a many to many and need to save (its a tags for a blog). But I am confused by your example. You show how to add many to many by creating the Book object by getting books from the database older than year 2000. BUT I am not getting them from a database... my data will be passed with the form (I suspect most peoples would be).

So where you have the following:

Code:

$b = new Book();
$b->where('year', 2000)->get();

How would I put my form submitted values for "books" (in my case tags) into this object, instead of using the where()->get()?

Here is the code I am trying to use. IU have spent ages trying all sorts of ways of doing this.

Code:

// Get user foo
$u = new User();
$u->where('username', 'foo')->get();

// Get a number of books from the year 2000
$b = new Book();
$b->where('year', 2000)->get();

// Relate user foo to all the books
$u->save($b->all);

Thanks a lot

[El Forum]

[eluser]WanWizard[/eluser]
You're posting in a Datamapper 1.6.0 thread. I sure hope you're using something more modern then that!

You're not entirely clear what exactly is posted, but I assume those tags are new entries that needs to be saved and related to something?

If so, then you need to create the tag objects first, populate them with the data posted, save them, and then relate them. You can do that in a similar way:

Code:

// array to store the new objects
$objects = array();

// assume tags are posted as an array
foreach ($this->input->post('tags') as $tag)
{
    $object = new Tag();
    $object->tagname = $tag;
    $object->save();
    $objects[] = $object;
}

// relate all the new objects to the parent object
$parent->save($objects);

[El Forum]

[eluser]amityweb[/eluser]
Ah sorry about that, I just found what seemed to be the support thread. I am using the current version. I cant see any links on your site to support, is there an official one?

As for the code, thanks a lot. But if I edit a blog now, it saves all the existing tags as new tags, and not updates any. I thought the save handled an update.

Also, is Datamapper intelligent enough to know I have deleted some tags, so to delete the relationships, BUT leave the tags in if used on other blogs, or delete the tags if not used on other blogs? This is the but that got me all confused when using native codeigniter queries and why I wanted an ORM. Otherwise I would still have to write all these functions to check for existing tags and delete or not where necessary. Basically, fully handle many to many relationships.

Thanks

[El Forum]

[eluser]WanWizard[/eluser]
There's a thread here for every version, a search for "Datamapper 1.8.2" would have revealed this thread as the first result.

No, there is no built-in logic to do 'diffs' on input. There are a million possible chunks of application logic related to ORM data, it's impossible to build it all in.

The best solution is to add a custom method to your Blogs model, which takes the list of tags as input, selects all currently related Tags, and loops over them. If not in the input, delete the relation (or the tag), if present in the input, delete it from the input. After the loop, anything left in the input are new tags, which can then be added.

If this is functionality you need often and for different models, consider writing a generic extension for it, so your new method will be available as an extra Datamapper method on all your models.

[El Forum]

[eluser]amityweb[/eluser]
Ok thanks. Glad I asked now, I assumed it would take care of it and would have spent ages trying to get it to work.

It is not as simple as deleting what is not in the new post data though, because its a many to many. So the tags may be used on other blogs. So if the tags are not used on another blog, OK to delete, if the tags are used then not ok to delete, and just delete the relationship. So probably not much more coding, but a little. Thing is, this is the trickiest part and why I wanted ORM for many to many.

[El Forum]

[eluser]WanWizard[/eluser]
You still have the many to many, so I don't see the issue.

Code:

// create the new blog entry (or code to retrieve an existing blog entry)
$blog = new Blog();
$blog->message = $this->input->post('message');
$blog->save();

// link the posted tags to the blog
$blog->link_tags($this->input->post('tags'));

and then in your blogs model add:

Code:

public function link_tags($tags)
{
    // get the existing tags
    $this->tags->get();

    // loop over them
    foreach ($this->tags as $tag)
    {
        // delete the relation for tags no longer there
        if ( ! in_array($tag->tag, $tags))
        {
            $this->delete($tag);
        }
        else
        {
            unset($tags[array_search($tag->tag, $tags)]);
        }
    }

    // now $tags contains only new tags
    $newtag = new Tag();
    foreach ($tags as $tag)
    {
        // get the tag, or create it if it doesn't exist
        $newtag->get_by_tag($tag);
        if ( ! $newtag->exists() )
        {
            $newtag->tag = $tag;
            $newtag->save();
        }

        // relate it
       $this->save($newtag);
    }

    // return for chaining
    return $this;
}

(or something like this, I'm just typing this in here, no testing has been done )