23374

CakePHP Pagination with conditions on has_many

So I have a User table and a History table with User hasMany Histories, and I'm trying to implement pagination on the user table.

My problem is that I have search, and some of the things one can search by are things in the History table. Is there a way to filter pagination results based on data in a table associated by hasMany? Containable, which initially seemed like a solution, allows such filtering but only in the retrieval of associated data, not the records themselves (unless I'm missing something?)

Has anyone had to solve this before?

Answer1:

Since it's a hasMany relationship, that means Cake will need to make 2 separate queries: 1 on the users table, and one on the histories table to retrieve all the associations. Since the History data isn't being retrieved until the 2nd query, then your 1st query cannot be filtered via WHERE conditions for fields found in the History model.

To resolve this, you can do one of two things:

<ol> <li>

Perform pagination on History using Containable (since History belongsTo User, meaning only 1 query will be performed).

</li> <li>

Perform pagination on User the way you're already doing, except perform an ad-hoc join to History such that it's no longer a hasMany relationship.

</li> </ol>

e.g.:

$this->User->bindModel(array('hasOne' => array('History'))); $this->paginate['User']['contain'][] = 'History'; $this->paginate('User', array('History.some_field' => 'some_value'));

Recommend

  • CakePHP and HABTM Model Limit Error
  • how to set containable behavior + cakephp2.x
  • Containable won't filter 2nd level model with HABTM & hasMany
  • Using the CakeDC search plugin with associated models
  • Database Table Schema and Aggregate Roots
  • How to send correlation id, into message, from sender and retrieval from receive into message header
  • Angular Material - Getting index of row in data table
  • Laravel - Dynamic relationship using hasManyThough() and unique merge
  • App Script HtmlService use with font like Google Web Fonts?
  • Is there a greater chance to collide when comparing GUIDs based on a hash vs \"Guid.NewGuid()?
  • Customize the “about” widget in hugo academic theme
  • Best way to store an image from a url in php?
  • writing unicode to binary file in python
  • Change paused animation's play state to running on click of a link
  • MVC - Dynamically loading Partial Views
  • Is it possible to collect a stream to two different collections using one line?
  • Modify a Google App Engine entity id?
  • How to set the navigation bar to the top in Table View?
  • Java Garbage collection, setting reference to null
  • ASP.NET windows authentication should always ask for credentials
  • Is there any purpose for h2-h6 headings in HTML5?
  • Lua: Line breaks in strings
  • C++ cout and enum representations
  • SQL: Getting the physical size of a subset of a table
  • Facebook iOS SDK Not Calling Completion Handler
  • How do I remove all but some records based on a threshold?
  • How can I run DataNucleus Bytecode Enhancer from SBT?
  • How do I get the list of bad records that didn't load in Bigquery?
  • How can I replace the server in Web Component Tester
  • CakePHP ACL tutorial initDB function warnings
  • Sequential (transactional) API calls in angular 4 with state management
  • Fetching methods from BroadcastReceiver to update UI
  • Check if a string to interpolate provides expected placeholders
  • Does CUDA 5 support STL or THRUST inside the device code?
  • RestKit - RKRequestDelegate does not exist
  • Traverse Array and Display in markup
  • Proper folder structure for lots of source files
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • File not found error Google Drive API
  • Is it possible to post an object from jquery to bottle.py?