Different types of search in Acumatica

Hello everybody,

recently friend of mine gave me wonderful question:

In PXSelect command, I saw Search, Search2, Search3… keywords, please explain the difference.

Thats really good question which shows his attentiveness to details.

So, no let's go part by part.

Targets

First of all, Search statement can be applied to those kinds of attributes: PXSelector, PXDbScalar and PXDefault. 

Also you can apply Search statement to cases when you updated something in cache of Acumatica and what to reopen that part. It can look like this:

Document.Search<POOrder.orderNbr>(currentPoOrder.OrderNbr, currentPoOrder.OrderType);

or like this:

[PXDefault(typeof (Search<Company.baseCuryID>))]

Don't mix attribute use with search in graph. The first form you can use in your methods, while second you can use in DAC class. 

As attributes

If to speak about attributes Search allows you to set specific value of of field in your DAC class. In other words it selects exact field rather then a record. The field specification goes as paramter. If to speak about syntax it is identical to PXSelect.

Following search options exist:

Type of search Description
Search<Field> Gets field value
Search<Field, Where> Gets field value with filtering by Where condition
Search<Field, Where, OrderBy> Gets field value with filtering by Where condition and ordering
Search2<Field, Join> Gets field value with filtering by using Joins with other tables
Search2<Field, Join, Where> Gets field value with filtering by using Joins with other tables and applying where condition
Search2<Field, Join, Where, OrderBy> Gets field value with filtering by using Joins with other tables and applying where condition and ordering
Search3<Field, OrderBy> Gets field with ordering application
Search3<Field, Join, OrderBy> Gets field value with joins and order by application
Search4<Field, Aggregate> Gets aggregated field value
Search4<Field, Where, Aggregate> Gets aggregated field value with filtering by where condition
Search4<Field, Where, Aggregate, OrderBy> Gets field value with filtering by where, aggregation and order by
Search5<Field, Join, Aggregate> Gets field value fiwth application of joins and aggregateds
Search5<Field, Join, Where, Aggregate> Gets field value with application of joins and where and aggregate
Search5<Field, Join, Where, Aggregate> Gets field value based on join, where and aggregate condition
Search6<Field, Aggregate, OrderBy> Gets field value based Aggregate and order by
Search6<Field, Join, Aggregate, OrderBy> Gets field value based on join, aggregate and order by
Coalesce<Search1, Search2> Gets field value with using Search1 or if Search1 gives null uses Search2

I hope with this table you can now better understand which search to use

7 Comments

  • Dmitrey Makarov-Paton said

    Thanks, Yuriy, great post. Please post more. Pls, can you clarify one thing though?

    Case 1. (DataView.Search) Does the query executed against the database every time or it searches the caches first? What happens on the consecutive calls to the search.

    Case 2.
    Does the query executed when the item inserted into the cache only.? What happens on the consecutive calls also. Does the cache of the type specified is searched?

  • docotor said

    Hello Dmitrey,
    for case 1 queries will be executed first time against db, other times if possible against cache in order to minimize load on db.
    For case 2.I'm not sure that I understand it. Can you please add a bit more description to your question

  • Dmitrey said

    Hi Yuriy, thank you for answering. I'm observing a different behaviour though. Perhaps you can spot an issue. Please see my contrived example below. My observations as follows...
    1. DataView.Search always executes SELECT TOP(1) ... for a new key value. Regardless, where there items of this type in the cache.
    2. Same behaviour for Search in PXDefault

    Please try the code below. Set up SQL PROFILER to for RPC:Competed & SQL:BatchCompleted events.
    Thanks. Keep up with your awesome posts.

    public class DataContext : PXGraph<DataContext>, IDisposable {

    public PXSelect<PriceList> PriceList;
    public PXSelect<ParkingLot> ParkingLots;

    public void Init() {
    PriceList.Select();
    }

    public void Dispose() => this.Clear();
    public ParkingLot CreateParkingLot() => (ParkingLot)ParkingLots.Cache.CreateInstance();
    }


    public class Test {

    public void DoThings1() {

    using (DataContext dc = PXGraph.CreateInstance<DataContext>()) {
    dc.Init(); // Select all prices

    int parkingLotId = 2;
    dc.ParkingLots.Select(); // Database is quired, how many records you have in db now in cache
    ParkingLot item = dc.ParkingLots.Cache.Cached.Cast<ParkingLot>().FirstOrDefault(row => row.ParkingLotID == parkingLotId);
    Debug.Assert(item != null, "Must in the cache");
    dc.ParkingLots.SetValueExt<ParkingLot.grade>(item, "B"); // Your other example! item's status does not change to Updated!!

    //.....
    var instance1 = (ParkingLot) dc.ParkingLots.Search<ParkingLot.parkingLotID>(parkingLotId); // Database queried again. SELECT TOP(1) ... WHERE ID=1
    Debug.Assert(Object.ReferenceEquals(instance1, item), "Same");
    Debug.Assert(instance1.ParkingLotID == item.ParkingLotID, "Same");

    //....
    var instance2 = (ParkingLot) dc.ParkingLots.Search<ParkingLot.parkingLotID>(parkingLotId); // 2nd call, Database NOT queried here unless you change ID to 2.
    Debug.Assert(Object.ReferenceEquals(instance1, instance2), "Same");


    var instance3 = dc.ParkingLots.Insert(dc.CreateParkingLot().With(r => r.Grade = "C")); // Triggers query on pxdefault
    Debug.Assert(Decimal.Compare(instance3.PricePerDay.Value, 5) == 0);


    var instance4 = dc.ParkingLots.Insert(dc.CreateParkingLot().With(r => r.Grade = "B")); // Triggers query on pxdefault
    Debug.Assert(Decimal.Compare(instance4.PricePerDay.Value, 10) == 0);
    }
    }
    }


    public class PriceList : IBqlTable {
    public abstract class grade : IBqlField { }
    [PXDBString(1, IsKey = true)]
    public string Grade { get; set; }

    public abstract class price : IBqlField { }
    [PXDBDecimal]
    public decimal? Price { get; set; }
    }

    /// <summary>
    /// </summary>
    [Serializable]
    public class ParkingLot : IBqlTable {
    public abstract class parkingLotID : IBqlField {}
    [PXDBIdentity(IsKey = true)]
    [PXUIField(DisplayName = "ID", Enabled = false, Visible = false)]
    [PXDefault]
    public virtual int? ParkingLotID { get; set; }


    public abstract class grade : IBqlField { }
    [PXDBString(1, IsFixed = true)]
    [PXDefault("A")]
    public virtual string Grade { get; set; }


    public abstract class pricePerDay : IBqlField { }
    [PXDBDecimal()]
    [PXDefault(typeof(Search<PriceList.price, Where<PriceList.grade, Equal<Current<ParkingLot.grade>>>>))]
    public virtual decimal? PricePerDay { get; set; }
    }


    create table [dbo].[PriceList](
    [Grade] [nchar](1) NOT NULL PRIMARY KEY,
    [Price] [decimal](18, 2) NOT NULL,
    )

    create table [dbo].[ParkingLot](
    [ParkingLotID] [int] IDENTITY(1,1) NOT NULL primary key,
    [Grade] [nchar](1) NULL,
    [PricePerDay] [decimal](18, 2) NULL,
    )

  • docotor said

    Hi Dmitrey,
    thanks for valuable test and comment. You are right.

  • docotor said

    Just want to add. The query that always execute against database are PXSelectReadOnly and it's modifications. Other kind of queries can execute against db, but also can omit db, and read everything from cache or part from cache and part from db.

  • Dmitrey Makarov-Paton said

    Thanks Yuri. Where do you work? What's your skype/email?

  • docotor said

    My skype is zaletskiy. For now I work as remote contractor and I mainly specialize on Acumaitca extending. I've send you an email message also.

Add a Comment