How to use withSum() with where condition

You can use withSum in query with where condition, here is an example:

If you want to list all Projects with the sum of all invoices that were issued this year:

$projects = Project::select('id', 'name', 'created_at')
            ->withSum(
                 ['invoices as prev_invoices' => function($query) {
                     $query->whereYear('invoices.issue_date', date('Y'));
                }], 'amount' 
             )
            ->get();

How to set a function in a model as an attribute

If you have a model with a function that you want to use it as an attribute, all you have to do is to use protected $appends in the Model class

For example, you have a Project class with a function that gets the sum of all invoices for that project

class Project extends Model
{
protected $appends = ['prev_invoices'];
....
public function getPrevInvoicesAttribute()       
    {
        $sum = Invoice::where('project_id', $this->id)
            
            ->sum('amount');
        return $sum;
    } 

Note that the function name must be formatted like that: “get” at the beginning, “Attribute” at the end, and the name to be CamelCase.

Create your own custom Excel function

If you want to make your own function as the built-in ones SUM() for example you go to:
Developer tab and click on the Visual Basic icon

in the new window of Microsoft Visual Basic for Applications, you click on Insert > Module

Now you can write your code, for example, if you want to write a function that extracts the url of a link in a cell, the code could be like this

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

Now, you can use this function “GetURL” as any ordinary function in Excel.

Note that if you want to save the file, you have to save it as Excel Macro-Enabled Workbook (*.xlsm)

Visit this page for more information:

https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f

Laravel / PHP SOS

When your code is not working right and you have no idea what is wrong:

  • What is the language ?
    Yes, you heard me right, sometimes when you are writing javascript code inside a laravel/php page ..things could get mixed up
  • Remove the cache
    There are lots of cache types you need to clear depending on what you have changed, here are some commands to use:
    • php artisan config:cache
    • php artisan route:cache
    • php artisan view:cache
    • php artisan cache:clear
    • php artisan clear-compiled
    • composer dump-autoload
    • php artisan debugbar:clear
    • php artisan optimize
  • What is the type of the variables ?
    When you want to compare 2 variables and you are sure the result should be true, check the type of the variables, sometimes you could find that you are comparing a variable to an object or collection

How to get the current date as yyyy-mm-dd in Javascript

If you want to get the current date in the format of yyyy-mm-dd using Javascript you can do:

new Date().toISOString().split('T')[0];

Where new Date() gets the date as :
Fri Mar 11 2022 11:04:44 GMT+0200 (Eastern European Standard Time) {}

.toISOString() convert it to: 2022-03-11T09:03:38.056Z

and as we need only the date, we split it and get the first part by: .split(‘T’)[0]

How to send a set of customized emails using Gmail and Google sheets without any third-party tools

If you have a list of email addresses and want to send a customized email for each one you can use Google Apps Script to get emails and data from Google sheet and use a draft email in Gmail. Data in Google sheet can be used as variables, ie. {{email}} , {{user name}}, {{password}}

Here is a link with a ready-made code to use:

https://developers.google.com/apps-script/samples/automations/mail-merge

How to use “OR” while searching in datatables js

If you want to search for any of multiple strings in datatables , you can use the regex option, here is an example

 table.columns( 3 ).search('string A| String B', true, false).draw();

3 is the index of the column you want to search

String A, String B are the 2 strings you want to search any of them

true: to indicate that you want to use regex

false : tell datatables not to use smart search option as it uses regex and may conflict with the regex you are using

How to view raw SQL of Laravel query?

There are a number of ways to view the raw SQL statement of your Larael query builder

->dd()

A simple way to show you the SQL statement along with its binding is replacing ->get(), first(), .. with ->dd(), here is an example:

$users = User::select('name')
                  ->where('id', '<', 20)
                  ->where('is_active', 1)
                  ->dd();

and the output will be like this:

"select `name` from `users` where `id` < ? and `is_active` = ?"
array:2 [▼
  0 => 20
  1 => 1
]
Continue reading “How to view raw SQL of Laravel query?”

How to add an external URL in Laravel balde

If you have a url that you want to get from DB and put it into Laravel blade and you want it to be linkable you may face a problem if the link doesn’t contain http:// as the browser will treat it as a page inside the current website.

For ex. if the link is external.com the html will be :

in Blade :

<a href = "{{ $site_url }}"> {{ $site_url }} </a>

in html:

<a href = "external.com">exterlnal.com</a>

The problem here is that when you click on that link , the browser will go to : www.mysite.com/exterlan.com

which will give you a 404.

You may think that to need to add http:// or https:// but all you need to add is just the // but then what if the url is saved with http:// or https:// then you better check for it first

<a href="@if(!str_contains( "$site_url", '//'))//@endif{{$site_url}}" target='_blank'>
                      {{ $site_url }}
                    </a>

ps. str_contains is a PHP8 function