-1

I have 3 tables-

users(id,name,dob)
books(id,title,author)
issuedbooks(id,book_id,student_id,issue_date)

The relationship between user and book is many to many resulting in third table issuedbooks.

My models are-

class student extends DataMapper{
    var $table="students";
    var $has_many=array(
        "books"=>array(
            "class"=>"book",
            "join_table"=>"issuedbooks",
            "join_self_as"=>"student",
            "join_other_as"=>"book",
            "other_field"=>"students"
        )
    );
}

class book extends DataMapper{
    var $table="books";
    var $has_many=array(
        "students"=>array(
            "class"=>"student",
            "join_table"=>"issuedbooks",
            "join_self_as"=>"book",
            "join_other_as"=>"student",
            "other_field"=>"books"
        )
    );
}

This table issuedbooks has entry like-

id      student_id      book_id  issue_date
1       2               1        2013-07-18 
2       2               4        2013-07-16 
3       1               5        2013-07-18 
4       2               6        2013-07-18

Now I have to find out all those books which is opted by student with id 2 and issue_date 2013-7-17.

I've tried, but won't get any result.

$student=new student();
$student->get_by_id('2');
$student->books->include_join_fields()->get();
foreach($student->books as $book):

$book->where_join_field($student,'issue_date >',"2013-07-17")->get();

    echo $book->title." ".$book->join_issue_date."<br />";
endforeach;

Please help me out, where am I going wrong?

Prateek Shukla
  • 593
  • 2
  • 7
  • 27

2 Answers2

-1

Here is my solution for this problem, it's very simple I guess.
And also you don't have to create ID column in many-to-many table (issuedbooks).
issuedbooks.book_id AND issuedbooks.student_id must be primary_keys.

class SomeModel extends CI_Model
{
    public function getBooksByStudentIdAndDate($students_id, $date)
    {
        $students_id = (int) $students_id;
        $date = (date('Y-m-d', strtotime($date)) === $date) ? $date : false;

        if( ! $students_id OR ! $date)
            return array();

        $this->db->where('issuedbooks.students_id', $students_id);
        $this->db->where('issuedbooks.issued_date', $date);

        $this->db->select('
            users.name, 
            books.title, 
            issuedbooks.issued_date as date
        ');
        $this->db->join('users','issuedbooks.students_id=users.id');
        $this->db->join('books','issuedbooks.book_id=books.id');
        $this->db->order_by('issuedbooks.issued_date', 'asc');

        return $this->db->from('issuedbooks')->get()->result_array();
    }
}
lysenkobv
  • 566
  • 2
  • 5
  • 20
-1

I would do it like this:

$student=new student();
$books = $student->where('id',2)
             ->where_related_book('issue_date','2013-07-17')
             ->get()

foreach($books as $book){
    ...

Overall i think you overcomplicate your models a bit. As long as you adhere to the standards you do not need to point out join tables etc in your relations

jonixj
  • 397
  • 3
  • 11
  • It is giving Database Error Unknown column 'books_books.issue_date' in 'where clause' SELECT `students`.* FROM (`students`) LEFT OUTER JOIN `issuedbooks` books_issuedbooks ON `students`.`id` = `books_issuedbooks`.`student_id` LEFT OUTER JOIN `books` books_books ON `books_books`.`id` = `books_issuedbooks`.`book_id` WHERE `students`.`id` = '2' AND `books_books`.`issue_date` > '2013-07-17' – Prateek Shukla Jul 20 '13 at 13:53