[SOLVED]DataMapper ORM help with creating a dropdown from a table

[eluser]Cgull[/eluser]
Hello,

I am new to using Datamapper and I can't understand it all, need some help.

I have these tables:

provinces:
id
name

books:
id
name

books_provinces:
id
book_id
province_id

Each book can have many provinces and each province can have many books.

I can create a province and I can create a book.

I have a page with the list of all the provinces and on it a button for books.

The button for books opens a page with a list of all the books the province is linked to.

Now I want to create a page where the user can add books to a province.

I tried using the htmlform extension but got totally lost.

So I create a form without the extension but I don't know how to limit the books list to books that are not already linked to the province.

My code for populating the books dropdown is:

Code:

$books = new Book();
$array = $books->get();
  
foreach($array as $arr)
{
   $options[$arr->id] = $arr->name;
}

$this->data['books'] = $options;

Can someone plesae help?

This solved the problem I had in the second post (Adding another new book object for the not_in query):

Code:

$b = new Book();
  
// Get all books relating to the given province
$array = $b->where_related('province', 'id', $id)->get();
  
foreach($array as $a)
{
$names[] = $a->id;
}

$bb = new Book();// Solved the problem
$books = $bb->where_not_in('id', $names)->get();

foreach($books as $val)
{
$options[$val->id] = $val->name;
}

[eluser]Cgull[/eluser]
There are two books: 1 and 2
province 2 has book 1

I tried:

Code:

$book = new Book();
  
// Get all books relating to the given province
$array = $book->where_related('province', 'id', 2)->get();
foreach($array as $a)
{
  $names[] = $a->id;
}

$books = $book->where_not_in('id', $names);
$this->data['test'] = $books;

Dump of the names array:
array (size=1)
0 => int 1

The books array (

Code:

$books = $book->where_not_in('id',$names);

):
holds book 1 instead of book 2, the book that the province does not have.

Shouldn't where_not_in return the book that is not in the names array?

[eluser]WanWizard[/eluser]
You're second query doesn't do a get(), so $books contains whatever was in $book (which is the result of the previous query).

[eluser]Cgull[/eluser]
Ah ! Thank you

Meanwhile, changed my code again to:

Code:

//Get all books
   $b = new Book();
   $array = $b->get();

   //Check if there are books that are not linked to the province
   $options = array();
   foreach($array as $val)
   {
    $b = new Book($val->id);
    //Check if the book is not linked to the province
    if(!$b->is_related_to($province))
    {
       //Add to dropdown array
     $options[$val->id] = $val->name;
    }
   }

[eluser]WanWizard[/eluser]
That works, but fires a lot of queries (total number of books + 1), which is a very bad idea...

[eluser]Cgull[/eluser]
So was my first way better?

[eluser]WanWizard[/eluser]
Yes, that only runs two queries.