29716

CakePHP Query - Conditional Based on Contain Field

Question:

I am developing a system that holds reports from customer's computers and displays failures in a list. I am attempting to write a query that locates all systems that have currently failed or have failures in the past.

My model for Computers has a field that says last_report_pass that allows me to quickly find computers that failed on the current day. My Reports are associated with a computer ID and has a field called status that says whether it was a pass or fail.

I am attempting to write a query that will only show last_report_pass being 0, or failed, or show it if it has reports that were found and joined (meaning there were previous failures).

Here was my idea:

$computers = $this->Computers->find('all', [ 'conditions' => [ 'last_report_pass' => '0', 'COUNT(Reports) NOT' => '0' ], 'contain' => [ 'Reports' => [ 'conditions' => [ 'status' => '0' ] ] );

I do not know what to do from here. I could probably write this in SQL but am trying to stick with Cake's ORM Query Builder. Thanks in advance!

Answer1:

You will need to use matching its similar to contain, but it will filter by associated data:

It will be something like this

$query->distinct(['Computers.id']) ->matching('Reports', function ($q) { return $q->where(['Reports.last_report_pass' => 0]); });

Its important to notice that you will also have to contain the Reports table if you need to display some data which is on this table.

<a href="https://book.cakephp.org/3.0/en/orm/query-builder.html#filtering-by-associated-data" rel="nofollow">Reference</a>

Recommend

  • How can I serialize entities from an ASP.NET MVC site to XML?
  • Thrift Client can't connect
  • How to read a certificate from a USB token in C#
  • C++ Pointer Arrays
  • How to protect an asp:textbox from user input?
  • Wait for .each() .getJSON request to finish before executing a callback
  • passing parameter to DownloadStringCompletedEventHandler in C#
  • Configuring multiple DefaultJmslistenercontainerfactory
  • Avoid registering duplicate broadcast receivers in Android
  • How to merge keras sequential models with same input?
  • (Tcl/Expect) clear screen after exit
  • Faster Way To Simultaneously Iterate Over Rolling Window Of Two Or More Numpy Arrays?
  • What command do i need to pass in SabreCommandLLSRQ to get current price of PNR?
  • How to handle elastic beanstalk deployment so it uploads only changed files
  • JSR-330 support in Picocontainer : @Inject … @Named(\"xxx)
  • Creating a DropDownList
  • Check all values in string[] for length?
  • Who propagate bugfixes across branches (corporate development)?
  • How to use JavaScript to determine whether a file exists in a directory?
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Scrapy recursive link crawler
  • If I include Java 8 in my Android app does that affect which devices it will work on?
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • PHP: When would you need the self:: keyword?
  • KeystoneJS: Relationships in Admin UI not updating
  • Acquiring multiple attributes from .xml file in c#
  • How to set the response of a form post action to a iframe source?
  • Hits per day in Google Big Query
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • unknown Exception android
  • Is there any way to bind data to data.frame by some index?
  • Checking variable from a different class in C#
  • reshape alternating columns in less time and using less memory
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Binding checkboxes to object values in AngularJs
  • How can i traverse a binary tree from right to left in java?
  • How can I use `wmic` in a Windows PE script?
  • How can I use threading to 'tick' a timer to be accessed by other threads?