0

I have a complex joining like

A belongs to Many B threw AB table AB belongs to many C threw ABC table

Now my problem is how i can get data in controller A which contain result till table ABC. Not finding direct relation in A and table ABC

My database structure is

Table A (id, name, code) Table B (id, name, code) Table C (id, name, code) Table AB (id, a_id, b_id, fee) Table ABC (ab_id, c_id, opt_date, tno)

I can connect with B using below query but not sure how to get data for ABC

    $this->A->find('list',[
contain => ['B']
    ]);

Here i am changing it to my real scheme

1. courses (id, name, course_code, etc...)
2. sessions (id, name, status, start_date, end_date)
3. seats (id, name, description, metadata, status)
4. courses_sessions (id, course_id, session_id, programme_coordinator, academic_coordinator, status)
5. courses_sessions_seats (id, courses_session_id, seats_id, no_seats)

Current Relations

a) courses belongsToMany sessions using courses_sessions
b) courses_sessions belongsToMany seats using courses_sessions_seats

I don't know if case two is possible on not, but what i want is to show course details with available seats per category session wise.

Sandeep K.
  • 21
  • 5
  • Searching list does return just two fields - primary key field and display field as array [primary_key => display_field] no other fields will be selected. – icebreaker Aug 21 '17 at 14:17
  • https://book.cakephp.org/3.0/en/orm/associations.html#using-the-through-option is the thing to look in documentation – icebreaker Aug 21 '17 at 14:22

1 Answers1

0

In short it could look something like this

$this->A
    ->find()
    ->contain([
      'B' => ['C']
      ]);

or

$this->A
    ->find()
    ->contain([
      'B' => function($q) {
         return $q->contain(['C']);    
      }]);

You can read about it here in docs

EDIT

What I think about table schema

  1. courses_sessions
    • course_sessions belongsTo Course
    • course_sessions belongsTo Session
  2. courses_sessions_seats
    • courses_sessions_seats belongsTo course_sessions
    • courses_sessions_seats belongsTo seats

If you make models in this way, you will have more control over queries later.

EDIT2

To obtain all data from perspective of Courses case you use

$courses = $this->Courses
  ->find()
  ->contain([
    'CoursesSessions' => ['Sessions', 'SessionsSeats']
  ]);
foreach ($courses as $course) :
  // Your code here
endforeach;

It is that easy

icebreaker
  • 1,224
  • 17
  • 33
  • Friend this is not my condition. Its different then it. A belongsToMany B using AB (Joined Table) and AB (Joined Table) belongsToMany C using ABC table. – Sandeep K. Aug 22 '17 at 07:38
  • Thanks for your replay. Like Courses belongsToMany Session using CoursesSession table. Now in each session there is different no of seats for each Category. So Now CoursesSession belongsToMany Categories using CategoryCoursesSession table (using primary key of CoursesSession table). Hope now you get the correct situation. – Sandeep K. Aug 22 '17 at 07:43
  • If you have that complex twist in your database, you can (and IMHO should) reduce it to hasMany and belongsTo structure. It will be easier to maintain. And update question to your real schema or take enough time to make it understandable. – icebreaker Aug 22 '17 at 09:46
  • I changed the question with real case and current schema and requirement – Sandeep K. Aug 23 '17 at 05:49
  • Yes, Its design in same way. Please let me know if any change is required or how to get final data where i get course details till seats allocation session wise – Sandeep K. Aug 25 '17 at 09:09