title: In Eloquent, is there a way to use sql functions in relationship query
body: <p>I am working in Eloquent with Laravel, and I have a table named <code>invoices.</code></p>
  <p>Each invoice has a prefix that identifies the <code>dealer id</code>.</p>
  <p>For example,
  <code>01-1234</code> is an invoice from <code>dealer 01</code>. <code>02-5678</code> is an invoice from <code>dealer 02</code>.</p>
  <p>Further complicating things is that the dealer's id is not <code>01</code> or <code>02</code>, but <code>DEALER01</code> or <code>DEALER02</code>.</p>
  <p>I would like to know if it's possible to get a query like this to work, so that the invoice object has an associated dealer object, without having to write a lot of repetitive code to loop through the invoices and manually link them.</p>
  <pre class="lang-php prettyprint-override"><code>public function dealer() {
      return $this-&gt;hasOne(Invoice::class, 'dealerId', 'concat(\'DEALER\', substr(Invoice.invoice, 1, 2))')
  }
  </code></pre>
  <p>Here are a few fields from the table structure of invoice:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>invoice</th>
  <th>cust</th>
  <th>date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>01-1234</td>
  <td>1</td>
  <td>2021-06-05</td>
  </tr>
  <tr>
  <td>02-1235</td>
  <td>2</td>
  <td>2021-06-06</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And here are a few fields from the structure of dealer:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>dealerId</th>
  <th>county</th>
  <th>default_sales_acct</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>DEALER01</td>
  <td>Some county</td>
  <td>1234</td>
  </tr>
  <tr>
  <td>DEALER02</td>
  <td>Some other county</td>
  <td>5678</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The only fields needed to link the two tables in this case are dealerId and the first two characters of invoice.</p>