Jump to content

fastsol

Moderators
  • Posts

    817
  • Joined

  • Last visited

  • Days Won

    9

Everything posted by fastsol

  1. Wow you are amazing with mysql! That query seems to work just great. Almost seems to work a little faster too, just a smidge. Thank you so much!
  2. @Barand Thank you so much for the help. I've decided to use this version of your query with slight modifications that haven't impacted the performance from what I have tested. SELECT `a1`.`id` AS `id` FROM ( ( `customers` `a1` JOIN `customers` `b1` ON ( ( (`a1`.`email` = `b1`.`email`) AND(`a1`.`id` <> `b1`.`id`) AND( `a1`.`email` <> 'blah@example.com' ) AND( `b1`.`email` <> 'blah@example.com' ) ) ) ) JOIN `quotes` `q1` ON ( ( (`q1`.`customer_id` = `b1`.`id`) AND(`q1`.`purchased` = 1) ) ) ) UNION SELECT `a`.`id` AS `id` FROM ( ( `customers` `a` JOIN `customers` `b` ON ( ( (`a`.`phone` = `b`.`phone`) AND(`a`.`id` <> `b`.`id`) ) ) ) JOIN `quotes` `q` ON ( ( (`q`.`customer_id` = `b`.`id`) AND(`q`.`purchased` = 1) ) ) ) ORDER BY `id` The performance of that query isn't super for a single record pull but it's not terrible. It performs well enough for multi record pulls though. It doesn't seem like it can get any better, so I'm fine with it, have to be or the rest of the stuff won't work lol. One other thing that I wonder could be added to the query is 2 columns like `by_email` and `by_phone` where the values would be 1 or 0 depending on if the result came from finding it in the email or phone query OR if it found it in both queries then both those new columns would be 1. At this point I have no idea if this can even be done without performance issues. If you can help again that would be awesome.
  3. I tried your new query and yes it is much faster than mine when gathering a large number of records at a time, even when paging through the results it really shines. BUT it's still much slower than mine when grabbing a single record. Mine is still .0015 ish and your new one is .15 ish. Since I'll be doing both single and multi record queries this new query might be the way to go. With multi record pulls mine goes up in time exponentially where yours stays consistent at that .12-.15. I'd still like to get the single record pull down in time but it's liveable. I did email you my sql dumps, did you get them?
  4. Clearly we have something different between our table setups for this. I'll try to get you a version of my tables where the personal data is altered for privacy. Then hopefully you can find the cause behind the lag on my end.
  5. Nice example! Seems accurate enough in table layout to determine what I need figured out. The results you list for my query are the correct ones based on your table data. Customers 12 and 15 should be listed because the email or phone for those customers is the same as another customer that has a purchased of 1. Basically what I'm doing here is as new customers get added, it needs to check for previous customers and see if the email or phone is the same AND if any of the previous customers matching have also purchased. If that condition is true, then it returns the new customers id so that in the Eloquent relationship I simply need to see if a record in the view exists under the same customer_id that the model is for. This way I can easily determine if this customer is a repeat because they actually have purchased something and are not just another record in the db with similar data but still has never purchased. Querying the relationship will allow me to do an easy check like this: // The relationship will either return the database record or NULL // Obviously if it's NULL then the if statment fails and we know they aren't a repeat. if($customer->repeatCustomer){ // Do something } I ran your new query (minus the join for qa since that gives the wrong data back) and it's drastically slower when trying to view the records in phpmyadmin. My query can return a standard list of 25 in .45 seconds and when trying the same thing with yours is 12.9 seconds. Also just pulling a single record by a customer id that I know to be in the view returns in .0009 seconds but yours still takes 12.4 seconds to get the same single record. Here are the EXPLAINs for the 2 queries when gathering 25 rows, my query is the first image. Hopefully this information helps.
  6. So here is the query that I finally got to work in a way that I can handle. The only thing I see as a potential problem is that the query is extremely slow, at least for the initial saving of the view. It seems to perform well enough when querying for a single record from the view. SELECT `a`.`id` AS `customer_id` FROM `customers` `a` WHERE ( SELECT `b`.`id` FROM ( `customers` `b` JOIN `quotes` ON ( ( `b`.`id` = `quotes`.`customer_id` ) ) ) WHERE ( ( (`a`.`email` = `b`.`email`) OR(`a`.`phone` = `b`.`phone`) ) AND(`a`.`id` <> `b`.`id`) AND(`quotes`.`purchased` = 1) ) GROUP BY `a`.`email` ) If I run this query in the phpmyadmin outside the view just as a regular query it takes about 75 seconds to load as it scans the full 10k+ records. I have indexes on every column that is used in that query within its respective table. I know views can be labor intensive so maybe there isn't anything that can be done. Also I filled in all the records that were missing phone numbers so that it would stop tripping on that. Those records are pretty old and aren't really a concern with their accuracy anymore.
  7. @BarandThe problem with your recent query seems to be stemming from the fact that early records don't always have a phone number.
  8. If that's supposed to be the entire query for the view, then it doesn't work. It gives me a ton of results that are all identical with the same id and email and no phone number.
  9. I'm getting much much closer. I ended up using a View table to build the query and then a hasOne eloquent relationship which is working as expected at the moment. @Barand I used your query above in the view with some additions. The only thing that isn't working with the query is lets say a customer is listed more than once in the db with the same email, then they are listed in your query results BUT if a customer is listed more than once with different emails but same phone numbers then they are not listed in your query results. I need the customer to be listed in either of those cases. Here is what I currently have as the View query. select `customers`.`email` AS `email` ,`customers`.`phone` AS `phone` ,group_concat(`customers`.`id` separator ',') AS `duplicates` from (`customers` join `quotes` on((`quotes`.`customer_id` = `customers`.`id`))) where (`quotes`.`purchased` = 1) group by `customers`.`email`,`customers`.`phone` having (count(0) > 1) One other thing I noticed is that querying the view takes much longer than any other query being performed on the page, upwards of 72ms which isn't terrible but considering that all the other queries are no more than 1.4ms it's a huge difference. I'm going to guess that it's because the data needs to be populated in the view first before it can be returned since it's not a static table of data. Does that sound right? Or is the view query itself that is causing the lag?
  10. Because on several pages if I don't do it with eager loading it will cause an N+1 query problem. Plus I don't need or want to load this info on every page as there will also be many times I don't need the extra info. Maybe I'm splitting hairs on not needing all the info all the time. I'll consider it if there is no other way to do it.
  11. Or could this maybe be done with a pivot table or a view table and then ran through a eloquent relationship?
  12. This relationship query gets me the correct results but it doesn't when using with() or load() on the model for eager loading. return $this->hasMany($this, 'id')->where('customers.id', '<>', $this->id) ->orWhere(function($query){ $query->where('customers.id', '<>', $this->id) ->where(function($query){ $query->where('email', $this->email) ->orWhere('phone', $this->phone); }); }); So if I try the code below it only returns the original Customer in the repeatCustomer relation instead of all of them. $customer = Customer::with('repeatCustomer')->find(1887); But if I call the relationship AFTER and not via the with() or load() it returns the correct results. $customer = Customer::find(1887); $customer->repeatCustomer;
  13. Duplicate customers. The same person may have made multiple purchases under the same email or phone number in the Customers table. I need to do this with an Eloquent relationship though.
  14. So I have a Customers table with normal fields and a standard ID primary key field. I am trying to find a way through a relationship method to get a collection of repeat customers. Sounds simple and can easily be done with query like so public function repeatCustomer() { return $this->where('id', '<>', $this->id) ->where(function($query){ $query->where('emai', $this->email) ->orWhere('phone', $this->phone); })->get(); } But that can't be loaded or eager loaded as a relationship. I don't have nor do I really want to have a parent_id type field in the table as there are already 15k records that aren't directly related now. As you can see in the query above I'm simply needing to find related customers by same email or phone. I have tried the hasMany relationship but no matter what relationship method I've tried I can't get the query to ignore the fact that it wants to find the Customer record by the primary key of the current $this model. Like this select * from `customers` where `customers`.`id` = 1887 and `customers`.`id` is not null and (`email` = blah.com or `phone` = 123-456-7890 It needs to not use this part of the query to work and still return a relationship. `customers`.`id` = 1887 Can this even be done with relationships? If not then I don't think I can do this at all since it can't be eager loaded and will cause a N+1 problem on several pages of the site.
  15. Found it, on the hosting server the Master is listed as 500M but the Local version says 32M. Thank you, I will track down where that is being set.
  16. My local setup is WAMP and I don't know of anything specific that I changed on that setup to allow more than 32M of memory. My local setting is 128M in the ini file.
  17. Any ideas as to where on a system running WHM that I could find such a setting? I already called my host and the only thing they looked at was the ini setting.
  18. Yes I know. This is the blade file code: @extends('layouts.admin') @section('content') @include('layouts.partials.form-errors') <div class="row"> <div class="col-lg-2"> <a href="{{ route('admin.quotes.appointments.phone.create') }}" class="mr-4">Add Install Time</a> </div> <div class="col-lg-2"> <a href="{{ route('admin.quotes.appointments.off') }}" class="mr-4">Take Days Off</a> </div> <div class="col-lg-5"> <form action="{{ route('admin.quotes.appointments.index') }}" method="get" class="form-inline mb-2"> <input type="text" name="date" class="form-control datepicker mb-2" placeholder="Search Date" value="{{ request('date') }}"> <button class="btn btn-primary mt-0 ml-2 mb-2">Search</button> </form> </div> </div> @if($search) {!! alert('warning', 'Currently in Search Mode') !!} @endif @if($appointments->count()) <div id="backtotop">Back To Top</div> <form action="{{ route('admin.quotes.appointments.purchased') }}" method="post" id="mark-as-done"> @include('helpers.previous_page_input_full_url') @csrf <button class="btn btn-primary float-right mb-2">Mark Checked Installs As Done</button> @adminTable(['headings' => ['Date', 'Name', 'Vehicle', 'Product / Notes', 'Actions']]) @foreach($appointments->chunk(5) as $chunks) @foreach($chunks as $appointment) <tr> <td> @php $color = $appointment->install_time->lessThan(\Carbon\Carbon::tomorrow()) ? 'text-danger' : ($appointment->install_time->lessThan(\Carbon\Carbon::tomorrow()->addDay()) ? 'text-primary' : '') @endphp <span class="{{ $color }}">{{ $appointment->install_time->format(config('sitespec.scheduler.install_time_display_format')) }}</span> </td> <td> @if($appointment->deposit) <div class="customer-name"> <a href="{{ searchByUrl($appointment->deposit->quote->customer->name) }}" data-toggle="tooltip" data-placement="bottom" title="{{ $appointment->deposit->quote->customer->phone }}"> {{ $appointment->deposit->quote->customer->name }} @if($appointment->deposit->quote->customer->gift_name) &nbsp;{!! GIFT_ICON !!} @endif </a> <div class="customer-details"> {{ $appointment->deposit->quote->customer->phone }} @if($appointment->deposit->quote->customer->gift_name) <br><br> {{ $appointment->deposit->quote->customer->gift_name }} {!! $appointment->deposit->quote->customer->gift_phone ? '<br>'.$appointment->deposit->quote->customer->gift_phone : '' !!} {!! $appointment->deposit->quote->customer->gift_date ? '<br>'.$appointment->deposit->quote->customer->gift_date->format('m-d-Y') : '' !!} @endif </div> </div> @elseif(optional($appointment->phoneAppointment)->customer) <a href="{{ searchByUrl($appointment->phoneAppointment->customer) }}"> {{ $appointment->phoneAppointment->customer }} </a> @endif </td> <td class="text-center"> @if($appointment->deposit) {{ $appointment->deposit->quote->customer->strippedVehicle }} @if($appointment->deposit->quote->customer->vyear->wires->count()) <a href="{{ route('admin.wires.show', $appointment->deposit->quote->customer->vyear) }}">{!! INFO_ICON !!}</a> @else {!! RED_X !!} @endif @elseif(optional($appointment->phoneAppointment)->vehicle) {{ $appointment->phoneAppointment->vehicle }} @endif </td> <td class="text-center"> @if($appointment->deposit) {{ $appointment->deposit->quote->changedProduct->count() ? $appointment->deposit->quote->changedProduct->first()->name : $appointment->deposit->product->name }} @elseif(optional($appointment->phoneAppointment)->product) {{ $appointment->phoneAppointment->product->name }} @endif {{ $appointment->notes ? ' - '.$appointment->notes : '' }} </td> @php $bg = ($appointment->deposit && !$appointment->deposit->quote->purchased && now() > $appointment->install_time->addHour(2)) ? 'bg-warning' : ''; @endphp <td class="text-center text-md {{ $bg }}"> <a href="{{ $appointment->deposit ? route('admin.quotes.appointments.app.edit', $appointment->deposit) : route('admin.quotes.appointments.phone.edit', $appointment) }}" class="mr-2"> {!! EDIT_ICON !!} </a> @if($appointment->deposit && $appointment->deposit->quote->purchased) {!! CHECK_MARK !!} @else <a href="javascript:void(0)" class="delete_record mr-2" data-url="{{ route('admin.quotes.appointments.destroy', $appointment) }}" data-toggle="modal" data-target="#destroy_confirmation">{!! GARBAGE_ICON !!}</a> @if($appointment->deposit) <div class="custom-control custom-checkbox custom-control-inline"> <input type="checkbox" class="custom-control-input" id="purchased{{ $appointment->id }}" name="purchased[]" value="{{ $appointment->deposit->id }}"> <label class="custom-control-label" for="purchased{{ $appointment->id }}"></label> </div> @endif @endif </td> </tr> @endforeach @endforeach @endadminTable </form> @include('partials.modals.destroy_confirmation') @else {!! alert('warning', 'No Appointments scheduled at this time.') !!} @endif @endsection @section('scripts') <script> $(function () { $('[data-toggle="tooltip"]').tooltip() }); </script> @endsection And this is the query: $appointments = Appointment::with( 'deposit.quote.customer.vmake', 'deposit.quote.customer.vmodel', 'deposit.quote.customer.vyear.wires', 'phoneAppointment.product', 'deposit.product', 'deposit.quote.changedProduct' ) ->where(function($query) use($validator) { return $this->appointmentWhere($query, $validator); }) ->whereNull('cancelled') ->orderBy('install_time') ->get(); As you can see I'm loading a fair number of relationships. The query is in the Controller. The problem comes when it tries to render the blade file. It will get so far in rendering and then error out of memory. I'm using the chunk method but even setting the chunk to 5 or less doesn't solve the problem. I imagine the problem is arrising from trying to access the deep relationship data on so many lines multiple times. I've also tried something like this but it too didn't help. @php $customer = $appointment->deposit->quote->customer; @endphp <div class="customer-name"> <a href="{{ searchByUrl($customer->name) }}" data-toggle="tooltip" data-placement="bottom" title="{{ $customer->phone }}"> {{ $customer->name }} @if($customer->gift_name) &nbsp;{!! GIFT_ICON !!} @endif </a> <div class="customer-details"> {{ $customer->phone }} @if($customer->gift_name) <br><br> {{ $customer->gift_name }} {!! $customer->gift_phone ? '<br>'.$customer->gift_phone : '' !!} {!! $customer->gift_date ? '<br>'.$customer->gift_date->format('m-d-Y') : '' !!} @endif </div> </div> That was to reduce the number of relationship dives to get at the data.
  19. So I have a page that is loading a good amount of models and then looping those in the blade file. The issue I am trying to fix is on my hosting server I keep getting this error Symfony\Component\Debug\Exception\FatalErrorExceptionGET /admin/quotes/appointments Allowed memory size of 33554432 bytes exhausted (tried to allocate 53248 bytes This only happens on my hosting server which is a dedicated server with 8GB of meomry and the php.ini file is set to 500M already. BUT on my local laptop where I only have 128M set in the ini file and run the exact same data on the page it loads just fine. Why is the memory having an issue on my hosting server but not local that is set at a lower limit? I've already tried running the loop with the chunk method but it hasn't helped. I'm not having an N+1 issue either cause debugbar shows the same number of queries ran no matter how big the data set I pull from the db. This is a critical page in my site and it's busy season right now, so this is a very bad thing to have happen. Luckily it only affects one page in the admin area so my customers aren't affected by it.
  20. round was the one thing I didn't try, Thank you! This gives the correct result. $price = '278.53'; $cents = $price * 100; $end_result = (int)round($cents); // Gives me 27853 as expected. also works for 278.59 I knew the issue was a precision thing, I just couldn't find the right combination of functions to make it work. In the end it was so simple (as I figured it would be).
  21. So I am trying to take a string value of dollars (posted from a form) and times it by 100 to get the cents Integer value of the dollars. For some reason if I do this with something like 278.53 I end up with 27852. Same concept for 278.59 ends up as 27858. I don't understand why or how to make it work. I've tried many things and nothing has made it work. $price = '278.53'; // posted from the form $cents = $price * 100; // converting to cents. end_result = (int)$cents // This will end up being 27852 not 27853.
  22. This is the piece of information that made it work how I want in all environments. I now understand that in the boot() it was just adding the scope to the list but not actually running it at that moment. By putting the admin/* in the apply() of the scope itself the request()->path() had been populated and worked as expected in testing also. Thank you so very much, I've been beating my head for a while on this as I kept coming back to it when I'd make a new test that it was affecting. This is what I ended up with: class IsActiveScope implements Scope { /** * @param Builder $builder * @param Model $model * @return Builder|void */ public function apply(Builder $builder, Model $model) { if(!request()->is('admin/*')) { return $builder->where($model->getTable() . '.active', '=', 1); } return $builder; } } And just this in the boot of the model: public static function boot(){ parent::boot(); static::addGlobalScope(new IsActiveScope()); }
  23. It appears that I can set a static method within the unit test itself that does transfer over when doing $this->get(route(''blah));. So I should be able to do the middleware method as you suggested, but then manually set the $enabled to false within the test before using $this-get(). This is a workaround I guess, but it still feels icky since I having to go around the actual code that runs the site normally to make it work just for the test. Maybe it's a compromise I'll have to live with. I had previously tried your last suggestion with the env settings and that made it work in reverse where now the customer side of the code was failing and the admin side was passing because I still couldn't set a true of false value based on a condition, the env value was just set as soon as the test started. I know I'm no where near a "great" coder, especially with Laravel as this is only my second site using it, so many of these little quirks about testing and env are slightly new to me.
  24. Ok so I was wrong, my session method was not working either. I'm starting to see the issue here though. When I run the site normally the middleware is triggered BEFORE the route model binding, but when unit testing the middleware is triggered AFTER the route model binding. I have confirmed this by die dumping in multiple places in the chain to see which gets triggered first. The results are as I stated above, unit testing the model binding is first in the chain which is why no matter what I do here it will likely fail cause I am not able to set any variable/class/anything beforehand to tell the model not to use the scope. Like I said originally, this is only an issue during testing, and really only makes it so I can't test 1 thing on certain models on either the customer side or admin side depending on when the scope is being applied. So maybe I just give up on this and know that that 1 specific test can't be handled through unit testing. I may end up doing Dusk anyway, so it would be tested then correctly.
  25. I've been testing the last 30 minutes by making 2 middleware that uses session() to set the decision to add the scope. Session seems to be the only way to retain the decision throughout the application due to varying class scopes (not the model scope) and such. So far it's working in production and the quick unit tests I ran. I have to do some more testing to ensure it's working as I need, but so far it looks good. Thank you very much, I just needed an idea of how else to set the decision besides the request() and you helped me there.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.