Upload a MySQL dump and review its tables, relations, pivot tables, trees, types and aggregations as a draft.
Welcome back. This time, you'll bring in a whole database: every table, with its relations, in one import.
Our store also runs a trail journal, with guides and gear tips. Let's give it a home first: its own API. Click New API, name it Journal, give it a path, and save. Now, open Data Import, and click New or Draft SQL Import. Pick the Journal API. Its first version is selected. The journal's database is a MySQL dump: authors, topics, seasons, articles, and a newsletter list. Upload the file, and click Analyze SQL file.
Once it's analyzed, SearchAPI lists every table. Nothing is imported yet: your import is a draft, so you can review its settings first. Each real table becomes a model, with its own endpoints. A pivot table only links two others. Here, article seasons pairs each article with its seasons. It doesn't get a model of its own. Instead, it becomes a list inside one of them. You don't have to import everything. If a table has nothing to do with this API, remove it. Our newsletter list, for example: subscriber emails don't belong in the Journal API. Click the trash icon, then Yes, Remove It! Changed your mind? The plus icon adds it back. The alerts on the left show what still needs your attention. Let's go through them.
Most of them come from relations, so start with articles, the table that links the others. Click the menu icon to hide the sidebar, and make room for the fields. Its model name and endpoint path come from the table, and you can change both. Each foreign key is already a relation: the author and the topic become embedded objects. Give them clear names: author, and topic. Now the pivot table. Choose which side gets the list: articles, so each article holds its seasons. A yellow alert means an embedded table shows only its id. Tick Visible In Related Model on the author's name, so each article shows it inside its embedded author. The yellow alert is gone. Do the same for the season's name.
Next, topics. Each topic has a parent, so SearchAPI detects a tree, and picks parent id as the self-reference. This alert is red, not yellow. With an embedded table, the id still works: one more request gets you the rest. A visible field just saves you that request. But a tree holds a whole path of parents. With ids alone, you'd need a request for every level, just to show their names. So a tree needs a visible field. Tick Visible In Related Model on the name. Topics are ready.
Back in articles, each field's type comes from its SQL column: reading minutes is an integer, featured is a boolean, and the publish date is a date. A title is prose, not a code, so make it text. Now pick the aggregation fields. Relations are in the list too, so readers can filter by topic, by season, and by featured. One table still has a red alert. We'll come back to it.
Until every alert is resolved, Start import stays off. For now, every change is saved as you go, so you can leave at any time. Click the menu icon, open Data Import, and click New or Draft SQL Import. Your import waits here as a draft. In the next guide, we'll pick it up, fix that alert, and start the import.
Read the details behind this guide on the documentation page.