2

My client is asking me to show a custom field in the Inventory report (see attached screenshot).

As you can see I managed to add the field to the rendered table (InventoryReport.php) but I have little clue as to how I can query and pull the custom field's data (it's a simple text field).

Anyone tried doing this before? Or could you point me in the right direction?

enter image description here

  • Stian, is this report coming from a 3rd party module, or something you have written? Can you share InventoryReport.php, or if the file the actual query is in is seperate can you find that? What normally happens is that there will be a model or controller creating the data, a controller creating the tabel and adding it to the view (Which I beleive is tyour InventoryReport.php file). – Blatant Apr 13 '15 at 10:02
  • Hi, InventoryReport.php is part of the Exp-Resso Store module (exp-resso.com). File here: d1x0mwiac2rqwt.cloudfront.net/534fc838f134ab44f29c53049e05facd/… – Stian Karlsen Apr 14 '15 at 12:27
0

Awesome, thanks for extra info, we have everything in the one file so the next question is where in the DB is the data for this column coming from?

Getting the data

So first, as you have seen the function row creates an individual row from the $stock variable passed. This is called in the function run on row 77.

Now depending on if your new info is coming from the expresso tables, or other EE tables or otherwise elsewhere. We can see the query that gets the data for each row starting at line 45. For the example I'm going to pull the entry_id from channel_titles.

If it's expresso data (coming from store_stock, store_products, store_stock_options, store_product_options, store_product_modifiers or channel_titles) all you'll need to do is extend the select query part on line 50 (see example). If it's in another table you will need to add some more joins (lines 46 to 49), but if you let me know the table, column and relationship I'll edit my example.

lines 50 to 56

    ->select(array(
            'store_stock.*',
            'channel_titles.title',
            'channel_titles.entry_id AS example,
            $db->raw('price + coalesce(sum(opt_price_mod), 0) as price'),
            $db->raw('group_concat(concat(mod_name, ": ", opt_name) order by mod_order, opt_order separator ", ") as description'),
            $db->raw('price * stock_level as stock_value'),
    ))

See how I've added 'channel_titles.entry_id AS example,? so this will make a value in the array called 'example'. Next is display...

Displaying the new data

As you've already made room in the table this is easy:

line 103

    $stock->example,

replacing "CUSTOM FIELD". Thats it you should get your entry id's in the column!

You should be able to elaborate from here, as I said if you need some advise on how to extend the query into another table, o if you want some help linking "piece of data X" to this query let me know and I'll edit some more help in.

Word of Warning

You are editing a file within a 3rd party module, this means if you update the module you may potentually overwrite your changes here. Just be warned and remember this change next time you're updating :)

_Adding data from channel fields

As per the comments below, the below code snippets should get you most of the way there to retrieve a field value according to the product entry. Replace this code :

$query = Stock::join('channel_titles', 'channel_titles.entry_id', '=', 'store_stock.entry_id')
    ->join('store_products', 'store_products.entry_id', '=', 'store_stock.entry_id')
    ->leftJoin('store_stock_options', 'store_stock_options.stock_id', '=', 'store_stock.id')
    ->leftJoin('store_product_options', 'store_product_options.product_opt_id', '=', 'store_stock_options.product_opt_id')
    ->leftJoin('store_product_modifiers', 'store_product_modifiers.product_mod_id', '=', 'store_product_options.product_mod_id')
    ->select(array(
        'store_stock.*',
        'channel_titles.title',
        $db->raw('price + coalesce(sum(opt_price_mod), 0) as price'),
        $db->raw('group_concat(concat(mod_name, ": ", opt_name) order by mod_order, opt_order separator ", ") as description'),
        $db->raw('price * stock_level as stock_value'),
    ))
    ->where('channel_titles.site_id', config_item('site_id'))
    ->groupBy('store_stock.id');

With this :

$query = Stock::join('channel_titles', 'channel_titles.entry_id', '=', 'store_stock.entry_id')
    ->join('channel_data', 'channel_data.entry_id', '=', 'store_stock.entry_id')
    ->join('store_products', 'store_products.entry_id', '=', 'store_stock.entry_id')
    ->leftJoin('store_stock_options', 'store_stock_options.stock_id', '=', 'store_stock.id')
    ->leftJoin('store_product_options', 'store_product_options.product_opt_id', '=', 'store_stock_options.product_opt_id')
    ->leftJoin('store_product_modifiers', 'store_product_modifiers.product_mod_id', '=', 'store_product_options.product_mod_id')
    ->select(array(
        'store_stock.*',
        'channel_titles.title',
        'channel_data.field_id_30 AS entry_price_in',
        $db->raw('price + coalesce(sum(opt_price_mod), 0) as price'),
        $db->raw('group_concat(concat(mod_name, ": ", opt_name) order by mod_order, opt_order separator ", ") as description'),
        $db->raw('price * stock_level as stock_value'),
    ))
    ->where('channel_titles.site_id', config_item('site_id'))
    ->groupBy('store_stock.id');

This should add the value $stock->entry_price_in value tothe $stock variable. Not tested, but should get you going, don't forget to update the row() function ;)

PS> Don't forget to mark this as the answer if it solves your question. :D

| improve this answer | |
  • Thank you so much for walking me through this! The data comes from the channel field called {store_price_in} which has a field ID of 30. That field resides in the table called exp_channel_fields, but as I understand it, the actual data related to each entry resides in the table called exp_channel_data. So then I assume the entry id has to be matched with exp_channel_data and then grab the contents of field_id_30 column, which means we have to do another join. An updated example of that would be immensely appreciated! – Stian Karlsen Apr 15 '15 at 12:52
  • You are correct, regarding the stratagy for the join what I normally do is make a developer decision as to if the field ID is likely to change between deployments and enviroments. If you can't be sure the field ID is fixed you need to do a double join or a seperate query might be just as quick! If you're happy the FIeld ID can be fixed then as you summise we can hard code it and get the value of field_id_30 column. I'll add an example to my answer soon. – Blatant Apr 15 '15 at 14:22
  • Superb! The field_id_30 can be hard coded as it will be fixed – Stian Karlsen Apr 16 '15 at 12:27
  • Glad to hear Stian! So you're all sorted now then? Don't forget to select the correct answer! (I must have those valueless points!! hah!) – Blatant Apr 16 '15 at 12:30
  • Beautiful man! Marked as correct :) Just wanted to say to anyone trying to do the same thing that there is a tiny typo in your example: the ) should be removed from the end of $db->raw('price + coalesce(sum(opt_price_mod), 0) as price'), – Stian Karlsen Apr 16 '15 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.